4338 lines
171 KiB
Transact-SQL
4338 lines
171 KiB
Transact-SQL
CREATE procedure [dbo].[TEST_MERGED_PROCEDURE]
|
|
@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;
|
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
|
|
----------------------------------------------- C6MartPeriodico.PL_S43LineaSelfBF
|
|
SELECT '' as 'C6MartPeriodico.PL_S43LineaSelfBF', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
codconf as NumContratto,
|
|
isnull(ctv,0) as Controvalore,
|
|
--INIZIO Intervento Eligo Titoli
|
|
NomeProdotto as NomeProdotto,
|
|
--FINE Intervento Eligo Titoli
|
|
dbo.ToShortDateString(cast(datasott as varchar)) as DataSott
|
|
FROM
|
|
C6MartPeriodico.ANAG_SELF LS
|
|
WHERE
|
|
LS.rete= @Rete
|
|
AND LS.codfis= @CodiceFiscale
|
|
AND isnull(ctv,0) < 0
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D_S333Fonditalia80
|
|
Select
|
|
'' as 'C6MartPeriodico.PL_D_S333Fonditalia80', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale
|
|
,[DES_PROD]
|
|
,[COD_CONF]
|
|
,[QUOTA]
|
|
,[NUMEROQUOTE]
|
|
,[CONTROVALORE]
|
|
,[DATARIF]
|
|
,[DES_MAX]
|
|
,[QUOTA_MAX]
|
|
,[QUOTA_MAX_PERC]
|
|
,[DATARIF_MAX]
|
|
,[DES_PROT]
|
|
,[QUOTA_PROT]
|
|
,[NUMEROQUOTE_PROT]
|
|
,[CONTROVALORE_PROT]
|
|
from [C6martperiodico].[capprot]
|
|
WHERE [Rete] = @Rete
|
|
AND [Codfis] = @CodiceFiscale
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_S10PatrimonioFinanziario
|
|
SELECT '' as 'C6MartPeriodico.PL_S10PatrimonioFinanziario' , @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
A.BANCA,
|
|
A.ORDINAMENTO,
|
|
A.AssetClassId,
|
|
A.AssetClassName,
|
|
cast(A.Controvalore as decimal(19,5)) Controvalore,
|
|
cast(A.Percentuale as decimal(19,5)) Percentuale,
|
|
cast(ROUND(SUM(A.Controvalore) OVER (PARTITION BY 'TOTALE'),2) as decimal(19,5)) AS Totale
|
|
FROM
|
|
(
|
|
SELECT DISTINCT
|
|
CASE @Rete
|
|
WHEN 'S' THEN 'Sanpaolo Invest'
|
|
ELSE 'Banca Fideuram'
|
|
END AS Banca,
|
|
ASSETCLASS.ORDINAMENTO,
|
|
VALORIASSET.ID_ASSETCLASS AS AssetClassId,
|
|
ASSETCLASS.DESCRIZIONE AS AssetClassName,
|
|
ROUND(SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS),2) as Controvalore,
|
|
(
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) /
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE')
|
|
) * 100 AS Percentuale
|
|
FROM (
|
|
SELECT
|
|
ASSETPERC.ID_ASSETCLASS,
|
|
PATRBF.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM C6MART.PATRIMONIO_BF PATRBF
|
|
INNER JOIN C6MART.ASSET_PERC ASSETPERC
|
|
ON PATRBF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
--ELIMINO I CONTRATTI ASUL PERCHE' UTILIZZIAMO IL DETTGALIO CHE PRENDIAMO CON LA SECONDA QUERY
|
|
(PATRBF.TIPO_PRODOTTO NOT IN ('ASUL'))--,'CC'))
|
|
and patrbf.id_area <> 'CC'
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
-- MODIFICA PER AVERE ASSET PERCENTUALI SUI CC
|
|
-- SELECT ASSETPERC.ID_ASSETCLASS,
|
|
-- PATRBF.CTV*ASSETPERC.PERC as Controvalore
|
|
-- FROM C6MART.PATRIMONIO_BF PATRBF
|
|
-- INNER JOIN C6MART.ASSET_PERC ASSETPERC
|
|
-- ON PATRBF.COD_SOTTOPRODOTTO = ASSETPERC.COD_SOTTOPRODOTTO
|
|
-- AND PATRBF.COD_INTERNO = ASSETPERC.COD_INTERNO
|
|
-- AND ASSETPERC.LIVELLO = 1
|
|
---- LEFT OUTER JOIN C6Mart.ClientiConCCNegativo CCNegativo
|
|
---- ON PATRBF.RETE = CCNegativo.Rete
|
|
---- AND PATRBF.COD_FISCALE = CCNegativo.Cod_Fiscale
|
|
-- WHERE
|
|
-- --ELIMINO I CC CON SALDO NEGATIVO
|
|
-- --PATRBF.TIPO_PRODOTTO = 'CC'
|
|
-- PATRBF.ID_AREA = 'CC'
|
|
-- AND PATRBF.RETE = @Rete
|
|
-- AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
-- --AND CCNegativo.Cod_Fiscale IS NULL
|
|
-- UNION ALL
|
|
SELECT ASSETPERC.ID_ASSETCLASS,
|
|
ASUL.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM C6MART.DETTAGLIO_ASUL ASUL
|
|
INNER JOIN C6MART.ASSET_PERC ASSETPERC
|
|
ON ASSETPERC.COD_PRODOTTO = ASUL.COD_PRODOTTO
|
|
---ELIMINO GLI SKANDIA
|
|
AND ASUL.COD_ISIN_SOTT = ''
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
ASUL.RETE = @Rete
|
|
AND ASUL.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
SELECT ASSETPERC.ID_ASSETCLASS,
|
|
ASUL.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM C6MART.DETTAGLIO_ASUL ASUL
|
|
INNER JOIN C6MART.ASSET_PERC ASSETPERC
|
|
--PRENDO SOLO GLI SKANDIA
|
|
ON ASSETPERC.COD_ISIN = ASUL.COD_ISIN_SOTT
|
|
AND ASSETPERC.COD_MAF = ASUL.COD_MAF_SOTT
|
|
AND ASSETPERC.COD_INTERNO = ASUL.COD_INTERNO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
ASUL.RETE = @Rete
|
|
AND ASUL.COD_FISCALE = @CodiceFiscale
|
|
) VALORIASSET
|
|
INNER JOIN C6Mart.ASSETCLASS ASSETCLASS
|
|
ON ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
|
|
) A
|
|
--V Aggiunto controllo che il cvt sia maggiore di zero, altrimenti
|
|
-- viene visualizzato sul report una riga con 0
|
|
WHERE A.CONTROVALORE > 0
|
|
ORDER BY A.ORDINAMENTO
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_Prodotti_Non_Associati_Area_Inv
|
|
SELECT '' as 'C6MartPeriodico.PL_Prodotti_Non_Associati_Area_Inv', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
ISNULL(PATRBF.NOME_PROGETTO,'Prodotti non associati a progetti') AS NOME_PROGETTO,
|
|
ID_AREA as NEED_AREA,
|
|
SUM(CTV) AS CONTROVALOREATTUALE,
|
|
MAX(ISNULL(RISK.VAR_PERC_PTF,0)) AS var_needarea,
|
|
--PATRBF.ORDINAMENTO_PROGETTO as ORDINAMENTO_PROGETTO,
|
|
PATRBF.CHIAVE_PROGETTO as ORDINAMENTO_PROGETTO,
|
|
CASE
|
|
WHEN RISK.COPERTURA IS NULL THEN 'n.c.'
|
|
WHEN RISK.COPERTURA = 0 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS var_needareaString
|
|
FROM
|
|
C6MARTPERIODICO.PATRIMONIO_BF PATRBF
|
|
LEFT OUTER JOIN
|
|
C6MARTPERIODICO.RISCHIO_AGGREGATO RISK
|
|
ON PATRBF.RETE = RISK.RETE
|
|
AND PATRBF.COD_FISCALE = RISK.COD_FISCALE
|
|
AND RISK.COD_AGGREG = 'PIRAMIDE|Inv|'+ ISNULL(PATRBF.NOME_PROGETTO,'Prodotti non associati a progetti')
|
|
WHERE 1 = 1
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.id_AREA = 'Inv'
|
|
AND PATRBF.NOME_PROGETTO='Prodotti non associati a progetti'
|
|
GROUP BY
|
|
PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
ISNULL(PATRBF.NOME_PROGETTO,'Prodotti non associati a progetti'),
|
|
ID_AREA,
|
|
CHIAVE_PROGETTO,
|
|
CASE
|
|
WHEN RISK.COPERTURA IS NULL THEN 'n.c.'
|
|
WHEN RISK.COPERTURA = 0 THEN 'n.c.'
|
|
ELSE NULL
|
|
END
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D_S332FVI_CS80_CT80
|
|
SELECT '' as 'C6MartPeriodico.PL_D_S332FVI_CS80_CT80', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
A.Banca,
|
|
A.Ordinamento,
|
|
A.AssetClassId,
|
|
A.AssetClassName,
|
|
cast(A.Controvalore as decimal(19,6)) Controvalore,
|
|
A.Percentuale,
|
|
cast(ROUND(SUM(A.Controvalore) OVER (PARTITION BY 'TOTALE'),2) as decimal(19,6)) AS PatrimonioFinanziarioCTV
|
|
FROM
|
|
(
|
|
SELECT DISTINCT
|
|
CASE @Rete
|
|
WHEN 'S' THEN 'Sanpaolo Invest'
|
|
ELSE 'Banca Fideuram'
|
|
END AS Banca,
|
|
ASSETCLASS.ORDINAMENTO AS Ordinamento,
|
|
VALORIASSET.ID_ASSETCLASS AS AssetClassId,
|
|
ASSETCLASS.DESCRIZIONE AS AssetClassName,
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) AS Controvalore,
|
|
(
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) /
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE')
|
|
) * 100 AS Percentuale
|
|
FROM (
|
|
--PRODOTTI BF ESCLUSI GLI ASUL
|
|
SELECT
|
|
ASSETPERC.ID_ASSETCLASS,
|
|
PATRBF.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
PATRBF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
--ELIMINO I CONTRATTI ASUL PERCHE' UTILIZZIAMO IL DETTGALIO CHE PRENDIAMO CON LA SECONDA QUERY
|
|
(PATRBF.TIPO_PRODOTTO NOT IN ('ASUL'))--,'CC'))
|
|
and patrbf.id_area <> 'CC'
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
SELECT
|
|
ASSETPERC.ID_ASSETCLASS,
|
|
ASUL.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM
|
|
C6MartPeriodico.DETTAGLIO_ASUL ASUL
|
|
INNER JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
ASSETPERC.COD_PRODOTTO = ASUL.COD_PRODOTTO
|
|
---ELIMINO GLI SKANDIA
|
|
AND ASUL.COD_ISIN_SOTT = ''
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
ASUL.RETE = @Rete
|
|
AND ASUL.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
--CON SKANDIA
|
|
SELECT ASSETPERC.ID_ASSETCLASS,
|
|
ASUL.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM C6MartPeriodico.DETTAGLIO_ASUL ASUL
|
|
INNER JOIN C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
--PRENDO SOLO GLI SKANDIA
|
|
ON ASSETPERC.COD_ISIN = ASUL.COD_ISIN_SOTT
|
|
AND ASSETPERC.COD_MAF = ASUL.COD_MAF_SOTT
|
|
AND ASSETPERC.COD_INTERNO = ASUL.COD_INTERNO
|
|
AND rtrim(ASSETPERC.COD_SOTTOPRODOTTO) = ASUL.COD_SOTTOPRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
ASUL.RETE = @Rete
|
|
AND ASUL.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
--PRODOTTI TERZI A CATALOGO
|
|
SELECT
|
|
ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') ID_ASSETCLASS,
|
|
PATRTERZI.CTV*ISNULL(ASSETPERC.PERC,1) as Controvalore
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRTERZI.COD_PRODOTTO <> 'PRODOTTO NON IN CATALOGO'
|
|
AND PATRTERZI.RETE = @Rete
|
|
AND PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
--PRODOTTI TERZI NON A CATALOGO
|
|
SELECT
|
|
ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') ID_ASSETCLASS,
|
|
PATRTERZI.CTV*ISNULL(ASSETPERC.PERC,1) as Controvalore
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
C6MartPeriodico.ASSET_PERC_TERZI ASSETPERC
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO_TERZI = ASSETPERC.COD_PRODOTTO_TERZI
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRTERZI.COD_PRODOTTO = 'PRODOTTO NON IN CATALOGO'
|
|
AND PATRTERZI.RETE = @Rete
|
|
AND PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
) VALORIASSET
|
|
INNER JOIN
|
|
C6MartPeriodico.ASSETCLASS ASSETCLASS
|
|
ON
|
|
ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
|
|
where assetclass.descrizione in ('Monetario', 'Obbligazionario', 'Azionario')
|
|
) A
|
|
ORDER BY A.ORDINAMENTO
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_MP_S137BIS_FOI80 Total
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S137BIS_FOI80', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
DTTRIM,
|
|
DTRIFE,
|
|
RETE,
|
|
CODFIS,
|
|
DESPROD,
|
|
CODMAN,
|
|
CODCONF,
|
|
CODPROD,
|
|
SUBPROD,
|
|
ISIN,
|
|
CTV,
|
|
PREZZO,
|
|
QUOTE,
|
|
PREZZOMAX,
|
|
DTPREZZOMAX,
|
|
DESMAX,
|
|
PREZZOPROT,
|
|
CTRVIMPPROT,
|
|
DESPROT,
|
|
DESPRODTIT,
|
|
QUOTA_MAX_PERC
|
|
FROM C6MartPeriodico.FOI80
|
|
WHERE 1=1
|
|
AND RETE = @Rete
|
|
AND CODFIS = @CodiceFiscale
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.GESTIONE_S_Intermediari
|
|
SELECT DISTINCT '' as 'C6MartPeriodico.GESTIONE_S_Intermediari', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, INTERMEDIARIO
|
|
FROM C6Mart.PATRIMONIO_TERZI
|
|
WHERE RETE = @Rete
|
|
AND COD_FISCALE = @CodiceFiscale
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_S80BisEvoluzioneRischio
|
|
SELECT '' as 'C6MartPeriodico.PL_S80BisEvoluzioneRischio', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
Fase
|
|
,DataFase
|
|
,VarMassimoProfiloFinanziario
|
|
,VarComplessivo
|
|
,Copertura
|
|
,RISKCLASS
|
|
,RISKCLASSMAX
|
|
,RiskClassString
|
|
,RiskClassMaxString
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[TB_S80BisEvoluzioneRischio]
|
|
WHERE [Rete] = @Rete
|
|
AND [CodiceFiscale] = @CodiceFiscale
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S150Anagrafica
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S150Anagrafica', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
[Cliente]
|
|
,[DatadiNascita]
|
|
,[ETA]
|
|
,[Professione]
|
|
,[StatoCivile]
|
|
,[NucleoFamiliare]
|
|
,[Residenza]
|
|
,[CodiceFiscale]
|
|
,[CodicePB]
|
|
,[CodiceRete]
|
|
,[CodiceContratto]
|
|
,[StatoContratto]
|
|
,[DataSottoscrizione]
|
|
,[DataPerfezionamento]
|
|
,[FasciaCPP]
|
|
,[IndirizzoEmail]
|
|
,[ProduzioneReport]
|
|
,[Indirizzo]
|
|
,[CAP]
|
|
,[Citta]
|
|
,[Provincia]
|
|
,[Nazione]
|
|
,[PRESSO]
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[TB_S25Anagrafica]
|
|
WHERE [CodiceRete] = @Rete
|
|
AND [CodiceFiscale] = @CodiceFiscale
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S151ProfiloDiRischio
|
|
BEGIN
|
|
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 '' as 'C6MartPeriodico.PL_D2_S151ProfiloDiRischio', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
@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
|
|
END
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S152Patrimonio
|
|
BEGIN
|
|
DECLARE @PatrimonioBF DECIMAL(15,2)
|
|
DECLARE @PatrimonioTerzi DECIMAL(15,2)
|
|
DECLARE @PatrimonioImmobiliare DECIMAL(15,2)
|
|
DECLARE @CCBF DECIMAL(15,2)
|
|
DECLARE @PatrimonioAltro DECIMAL(15,2)
|
|
DECLARE @PatrimonioTotale DECIMAL(15,2)
|
|
DECLARE @Part_viaggianti decimal(15,2)
|
|
SELECT @PatrimonioBF = SUM(CTV)
|
|
FROM C6MartPeriodico.PATRIMONIO_BF patrBF
|
|
WHERE
|
|
patrBF.Rete = @Rete
|
|
AND patrBF.Cod_Fiscale = @CodiceFiscale
|
|
AND patrBF.id_area not in ('CC','SELF')
|
|
SELECT @Part_viaggianti = SUM(IMPORTO)
|
|
FROM
|
|
(
|
|
SELECT SUM(IMPORTO) AS IMPORTO
|
|
FROM C6Martperiodico.PARTITE_VIAGGIANTI partvia
|
|
WHERE
|
|
partvia.Rete = @Rete
|
|
and partvia.Cod_Fiscale = @CodiceFiscale
|
|
union
|
|
SELECT SUM(PARTVIA_DISINV) AS IMPORTO
|
|
FROM C6Martperiodico.patrimonio_bf partvia
|
|
WHERE
|
|
partvia.Rete = @Rete
|
|
and partvia.Cod_Fiscale = @CodiceFiscale
|
|
) T
|
|
SELECT @PatrimonioTerzi = SUM(ROUND(CTV,2))
|
|
FROM C6MartPeriodico.PATRIMONIO_TERZI patrTerzi
|
|
WHERE
|
|
patrTerzi.Rete = @Rete
|
|
and patrTerzi.Cod_Fiscale = @CodiceFiscale
|
|
SELECT @PatrimonioImmobiliare = SUM(patrImm.VALORE_STIMATO)
|
|
FROM C6MartPeriodico.PATRIMONIO_IMMOBILIARE patrImm
|
|
WHERE
|
|
patrImm.Rete = @Rete
|
|
and patrImm.Cod_Fiscale = @CodiceFiscale
|
|
SELECT @PatrimonioAltro = SUM(patrAltro.ctv)
|
|
FROM C6MartPeriodico.PATRIMONIO_ALTRO patrAltro
|
|
WHERE
|
|
patrAltro.Rete = @Rete
|
|
AND patrAltro.Cod_Fiscale = @CodiceFiscale
|
|
declare @summa as decimal(15,2)
|
|
SET @Part_viaggianti=coalesce(@Part_viaggianti,0)
|
|
SET @PatrimonioBF =coalesce(@PatrimonioBF,0)
|
|
SET @summa = @PatrimonioBF + @Part_viaggianti
|
|
SET @PatrimonioBF=@summa
|
|
SET @PatrimonioTerzi =coalesce(@PatrimonioTerzi,0)
|
|
SET @PatrimonioImmobiliare =coalesce(@PatrimonioImmobiliare,0)
|
|
SET @PatrimonioAltro= coalesce(@PatrimonioAltro,0)
|
|
SET @PatrimonioTotale = coalesce(@PatrimonioBF+@PatrimonioTerzi+@PatrimonioImmobiliare+@PatrimonioAltro,0)
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S152Patrimonio', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
@PatrimonioBF AS BFCTV,
|
|
@PatrimonioTerzi AS PatrimonioTerziCTV,
|
|
@PatrimonioBF +@PatrimonioTerzi AS FinanziarioCTV,
|
|
CONVERT(decimal(6,2),((case @PatrimonioTotale when 0.00 then 0.00 else (@PatrimonioBF + @PatrimonioTerzi)/ @PatrimonioTotale end) * 100.00)) AS FinanziarioPerc,
|
|
@PatrimonioImmobiliare AS ImmobiliareCTV,
|
|
CONVERT(decimal(6,2),((case @PatrimonioTotale when 0.00 then 0.00 else @PatrimonioImmobiliare/@PatrimonioTotale end ) *100.00)) AS ImmobiliarePerc,
|
|
@PatrimonioAltro AS AltroCTV,
|
|
CONVERT(decimal(6,2),(case @PatrimonioTotale when 0.00 then 0.00 else @PatrimonioAltro/@PatrimonioTotale end) *100.00) AS AltroPerc,
|
|
@PatrimonioTotale AS TotaleCTV
|
|
END
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S153PatrimonioFinanziario
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S153PatrimonioFinanziario', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
Intermediario,
|
|
Ordine,
|
|
Banca,
|
|
Controvalore,
|
|
round(Percentuale,2) as Percentuale
|
|
FROM
|
|
(
|
|
SELECT DISTINCT
|
|
Intermediario,
|
|
Ordine,
|
|
CASE @Rete WHEN 'F' THEN 'Banca Fideuram' ELSE 'Sanpaolo Invest' END AS Banca,
|
|
SUM(ctv) over (partition by Intermediario,Ordine) AS CONTROVALORE,
|
|
(
|
|
(SUM(ctv) over (partition by Intermediario,Ordine)) /
|
|
(SUM(ctv) over (partition by 'Totale'))
|
|
) * 100 AS PERCENTUALE
|
|
FROM
|
|
(
|
|
SELECT
|
|
CASE @Rete WHEN 'F' THEN 'Banca Fideuram' ELSE 'Sanpaolo Invest' END AS Intermediario,
|
|
SUM(ROUND(CTV,2)) AS ctv,
|
|
1 AS ordine
|
|
FROM C6MartPeriodico.PATRIMONIO_BF patrBF
|
|
WHERE 1 = 1
|
|
AND patrBF.Rete = @Rete
|
|
AND patrBF.Cod_Fiscale = @CodiceFiscale
|
|
and patrBF.id_area not in ('CC','SELF')
|
|
UNION ALL
|
|
SELECT
|
|
Intermediario,
|
|
SUM(ROUND(CTV,2)) AS ctv,
|
|
2 AS ordine
|
|
FROM C6MartPeriodico.PATRIMONIO_TERZI patrTerzi
|
|
WHERE 1 = 1
|
|
AND patrTerzi.Rete = @Rete
|
|
AND patrTerzi.Cod_Fiscale = @CodiceFiscale
|
|
GROUP BY patrTerzi.Intermediario
|
|
) PATR
|
|
) A
|
|
WHERE CONTROVALORE IS NOT NULL
|
|
ORDER BY Ordine
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S154PatrimonioFinanziario
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S154PatrimonioFinanziario', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
A.Banca,
|
|
A.Ordinamento,
|
|
A.AssetClassId,
|
|
A.AssetClassName,
|
|
cast(A.Controvalore as decimal(19,6)) Controvalore,
|
|
A.Percentuale,
|
|
cast(ROUND(SUM(A.Controvalore) OVER (PARTITION BY 'TOTALE'),2) as decimal(19,6)) AS PatrimonioFinanziarioCTV
|
|
FROM
|
|
(
|
|
SELECT DISTINCT
|
|
CASE @Rete WHEN 'S' THEN 'Sanpaolo Invest'ELSE 'Banca Fideuram' END AS Banca,
|
|
ASSETCLASS.ORDINAMENTO AS Ordinamento,
|
|
VALORIASSET.ID_ASSETCLASS AS AssetClassId,
|
|
ASSETCLASS.DESCRIZIONE AS AssetClassName,
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) AS Controvalore,
|
|
(
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) /
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE')
|
|
) * 100 AS Percentuale
|
|
FROM (
|
|
SELECT --PRODOTTI BF ESCLUSI GLI ASUL
|
|
ASSETPERC.ID_ASSETCLASS,
|
|
PATRBF.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON PATRBF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
PATRBF.TIPO_PRODOTTO NOT IN ('ASUL')
|
|
AND PATRBF.ID_AREA not in ('CC','SELF')
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
SELECT
|
|
ASSETPERC.ID_ASSETCLASS,
|
|
ASUL.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM
|
|
C6MartPeriodico.DETTAGLIO_ASUL ASUL
|
|
INNER JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
ASSETPERC.COD_PRODOTTO = ASUL.COD_PRODOTTO
|
|
---ELIMINO GLI SKANDIA
|
|
AND ASUL.COD_ISIN_SOTT = ''
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
ASUL.RETE = @Rete
|
|
AND ASUL.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
--CON SKANDIA
|
|
SELECT ASSETPERC.ID_ASSETCLASS,
|
|
ASUL.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM C6MartPeriodico.DETTAGLIO_ASUL ASUL
|
|
INNER JOIN C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
--PRENDO SOLO GLI SKANDIA
|
|
ON ASSETPERC.COD_ISIN = ASUL.COD_ISIN_SOTT
|
|
AND ASSETPERC.COD_MAF = ASUL.COD_MAF_SOTT
|
|
AND ASSETPERC.COD_INTERNO = ASUL.COD_INTERNO
|
|
AND rtrim(ASSETPERC.COD_SOTTOPRODOTTO) = ASUL.COD_SOTTOPRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
ASUL.RETE = @Rete
|
|
AND ASUL.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
--PRODOTTI TERZI A CATALOGO
|
|
SELECT
|
|
ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') ID_ASSETCLASS,
|
|
PATRTERZI.CTV*ISNULL(ASSETPERC.PERC,1) as Controvalore
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRTERZI.COD_PRODOTTO <> 'PRODOTTO NON IN CATALOGO'
|
|
AND PATRTERZI.RETE = @Rete
|
|
AND PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
--PRODOTTI TERZI NON A CATALOGO
|
|
SELECT
|
|
ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') ID_ASSETCLASS,
|
|
PATRTERZI.CTV*ISNULL(ASSETPERC.PERC,1) as Controvalore
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
C6MartPeriodico.ASSET_PERC_TERZI ASSETPERC
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO_TERZI = ASSETPERC.COD_PRODOTTO_TERZI
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRTERZI.COD_PRODOTTO = 'PRODOTTO NON IN CATALOGO'
|
|
AND PATRTERZI.RETE = @Rete
|
|
AND PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
) VALORIASSET
|
|
INNER JOIN
|
|
C6MartPeriodico.ASSETCLASS ASSETCLASS
|
|
ON
|
|
ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
|
|
) A
|
|
ORDER BY A.ORDINAMENTO
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S155PatrimonioImmobiliare
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S155PatrimonioImmobiliare', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRIMONIO_IMMOBILIARE.DESCRIZIONE AS Descrizione,
|
|
PATRIMONIO_IMMOBILIARE.TIPOLOGIA AS Tipologia,
|
|
PATRIMONIO_IMMOBILIARE.LOCALITA AS Localita,
|
|
isnull(PATRIMONIO_IMMOBILIARE.AFFITTO,0) AS AffittoPercepito,
|
|
PATRIMONIO_IMMOBILIARE.valore_stimato AS Valore,
|
|
SUM(PATRIMONIO_IMMOBILIARE.VALORE_STIMATO) OVER (PARTITION BY 'Totale') as Totale,
|
|
SUM(isnull(PATRIMONIO_IMMOBILIARE.AFFITTO,0)) OVER (PARTITION BY 'TotaleAffitto') as TotaleAffitto
|
|
FROM C6MARTperiodico.PATRIMONIO_IMMOBILIARE
|
|
WHERE PATRIMONIO_IMMOBILIARE.RETE = @Rete
|
|
AND PATRIMONIO_IMMOBILIARE.COD_FISCALE = @CodiceFiscale
|
|
ORDER BY 5 DESC
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S156ALtroPatrimonio
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S156ALtroPatrimonio', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRIMONIO_ALTRO.DESCRIZIONE AS Denominazione,
|
|
PATRIMONIO_ALTRO.QUANTITA AS NumeroQuote,
|
|
PATRIMONIO_ALTRO.CTV AS Valore,
|
|
SUM(PATRIMONIO_ALTRO.CTV) OVER (PARTITION BY 'Totale') as Totale
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_ALTRO
|
|
WHERE
|
|
PATRIMONIO_ALTRO.RETE = @Rete
|
|
AND PATRIMONIO_ALTRO.COD_FISCALE = @CodiceFiscale
|
|
ORDER BY
|
|
PATRIMONIO_ALTRO.CTV DESC
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S157ConfrontoPiramidi
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S157ConfrontoPiramidi', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
[NeedArea]
|
|
,[AreaName]
|
|
,[PercentualeModello]
|
|
,[PercentualeAttuale]
|
|
,[ControvaloreAttuale]
|
|
,[ControvaloreModello]
|
|
,[Totale_Percentuale]
|
|
,[DifferenzaEuro]
|
|
,[DifferenzaPercentuale]
|
|
,[var_needarea]
|
|
,[var_needareaString]
|
|
,[copertura_needarea]
|
|
,[copertura_needareaString]
|
|
,[Nota]
|
|
,[somma_controvalAttuale]
|
|
,[somma_controvalModello]
|
|
,[somma_percentualeAttuale]
|
|
,[somma_percentualeModello]
|
|
,[var_tot]
|
|
,[var_totString]
|
|
,[var_tot_pir]
|
|
,[var_tot_pirString]
|
|
,[copertura_tot]
|
|
,[copertura_totString]
|
|
,[copertura_tot_pir]
|
|
,[copertura_tot_pirString]
|
|
,[VAR_RISFIN]
|
|
,[VAR_RISFINSTRING]
|
|
,[copertura_risfin]
|
|
,[copertura_risfinString]
|
|
,[ordinamento]
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[TB_S133_S157DatiPiramide]
|
|
WHERE [Rete] = @Rete
|
|
AND [CodiceFiscale] = @CodiceFiscale
|
|
order by ordinamento
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S158PiramideModello
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S158PiramideModello', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
BF.NOME_PROGETTO AS NomePrg,
|
|
PIRMOD.ORIZZ_TEMP AS OrizzTempPrg,
|
|
AGGR.VAR_PERC_PTF as VarPrg,
|
|
SUM(BF.CTV)AS ControvalorePrg
|
|
FROM C6MartPeriodico.PATRIMONIO_BF BF
|
|
INNER JOIN C6MartPeriodico.RISCHIO_AGGREGATO AGGR
|
|
ON BF.RETE= AGGR.RETE
|
|
AND BF.COD_FISCALE= AGGR.COD_FISCALE
|
|
AND AGGR.COD_AGGREG = 'RISFIN|PIRAMIDE|' + LEFT(BF.ID_AREA,3) + '|' + BF.NOME_PROGETTO
|
|
INNER JOIN C6MartPeriodico.PIRAMIDE_MOD PIRMOD
|
|
ON BF.RETE= PIRMOD.RETE
|
|
AND BF.COD_FISCALE= PIRMOD.COD_FISCALE
|
|
AND BF.ID_AREA = PIRMOD.ID_AREA
|
|
AND BF.NOME_PROGETTO = PIRMOD.NOME_PROGETTO
|
|
WHERE 1=1
|
|
AND BF.ID_AREA = 'inv'
|
|
AND BF.RETE = @Rete
|
|
AND BF.COD_FISCALE = @CodiceFiscale
|
|
AND BF.CTV > 0
|
|
group BY BF.NOME_PROGETTO,PIRMOD.ORIZZ_TEMP,AGGR.VAR_PERC_PTF
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S159PatrimonioFinanziario
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S159PatrimonioFinanziario', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
A.BANCA AS Banca,
|
|
A.ORDINAMENTO AS Ordinamento,
|
|
A.AssetClassId AS AssetClassId,
|
|
A.AssetClassName AS AssetClassName,
|
|
cast(A.Controvalore as decimal(19,5)) AS Controvalore,
|
|
cast(A.Percentuale as decimal(19,5)) AS Percentuale,
|
|
cast(ROUND(SUM(A.Controvalore) OVER (PARTITION BY 'TOTALE'),2) as decimal(19,5)) AS Totale
|
|
FROM
|
|
(
|
|
SELECT DISTINCT
|
|
CASE @Rete
|
|
WHEN 'S' THEN 'Sanpaolo Invest'
|
|
ELSE 'Banca Fideuram'
|
|
END AS Banca,
|
|
ASSETCLASS.ORDINAMENTO,
|
|
VALORIASSET.ID_ASSETCLASS AS AssetClassId,
|
|
ASSETCLASS.DESCRIZIONE AS AssetClassName,
|
|
ROUND(SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS),2) as Controvalore,
|
|
(
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) /
|
|
--16.07.2011 COLAIANNI CONSOLI GESTIONE DIVISIONE PER ZERO
|
|
CASE WHEN ( SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE') ) = 0
|
|
THEN 1
|
|
ELSE ( SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE') )
|
|
END
|
|
) * 100 AS Percentuale
|
|
FROM (
|
|
SELECT
|
|
ASSETPERC.ID_ASSETCLASS,
|
|
PATRBF.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
PATRBF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
--ELIMINO I CONTRATTI ASUL PERCHE' UTILIZZIAMO IL DETTGALIO CHE PRENDIAMO CON LA SECONDA QUERY
|
|
(PATRBF.TIPO_PRODOTTO NOT IN ('ASUL'))--,'CC'))
|
|
and patrbf.id_area not in ('CC','Self')
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
SELECT
|
|
ASSETPERC.ID_ASSETCLASS,
|
|
ASUL.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM
|
|
C6MartPeriodico.DETTAGLIO_ASUL ASUL
|
|
INNER JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
ASSETPERC.COD_PRODOTTO = ASUL.COD_PRODOTTO
|
|
---ELIMINO GLI SKANDIA
|
|
AND ASUL.COD_ISIN_SOTT = ''
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
ASUL.RETE = @Rete
|
|
AND ASUL.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
SELECT ASSETPERC.ID_ASSETCLASS,
|
|
ASUL.CTV*ASSETPERC.PERC as Controvalore
|
|
FROM C6MartPeriodico.DETTAGLIO_ASUL ASUL
|
|
INNER JOIN C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
--PRENDO SOLO GLI SKANDIA
|
|
ON ASSETPERC.COD_ISIN = ASUL.COD_ISIN_SOTT
|
|
AND ASSETPERC.COD_MAF = ASUL.COD_MAF_SOTT
|
|
AND ASSETPERC.COD_INTERNO = ASUL.COD_INTERNO
|
|
AND rtrim(ASSETPERC.COD_SOTTOPRODOTTO) = ASUL.COD_SOTTOPRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE
|
|
ASUL.RETE = @Rete
|
|
AND ASUL.COD_FISCALE = @CodiceFiscale
|
|
) VALORIASSET
|
|
INNER JOIN
|
|
C6MartPeriodico.ASSETCLASS ASSETCLASS
|
|
ON
|
|
ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
|
|
) A
|
|
ORDER BY A.ORDINAMENTO
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S160DettaglioProdottiBF
|
|
BEGIN
|
|
--INIZIO INTERVENTI OMNIA QUIII
|
|
DECLARE @TOTSELFLIQ AS INT
|
|
SELECT @TOTSELFLIQ = COUNT(*)
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF
|
|
WHERE PATRIMONIO_BF.RETE = @Rete
|
|
AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
|
|
AND ISIN in('EURO00000009', 'EURO10000007')
|
|
AND ID_AREA = 'LIQ'
|
|
DECLARE @TOTLIQ AS INT
|
|
SELECT @TOTLIQ = COUNT(*)
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF
|
|
WHERE PATRIMONIO_BF.RETE = @Rete
|
|
AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
|
|
AND ID_AREA = 'LIQ'
|
|
--FINE INTERVENTI OMNIA QUIII
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S160DettaglioProdottiBF', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Pre'
|
|
ELSE AREA.ID_AREA
|
|
END AS needarea,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Previdenza'
|
|
WHEN AREA.ID_AREA = 'Na' THEN 'Non allocate'
|
|
ELSE AREA.NOME_AREA
|
|
END AS needareades,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 3
|
|
ELSE AREA.ORDINAMENTO
|
|
END AS areasortorder,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND PATRBF.NOME_PROGETTO IS NULL THEN 'Prodotti non associati a progetti'
|
|
ELSE ltrim(rtrim(PATRBF.NOME_PROGETTO))
|
|
END AS nome_progetto,
|
|
PATRBF.CHIAVE_PROGETTO as ORDINAMENTO_PROGETTO,
|
|
SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA) AS somma_controval_needarea,
|
|
SUM(PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA) AS versato_needarea,
|
|
CASE
|
|
WHEN COUNT(CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END as versato_needareaString,
|
|
SUM(MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA) AS MinusPlusValenza_needarea,
|
|
CASE
|
|
WHEN COUNT( CASE
|
|
WHEN MONIT.MINUS_PLUSVALENZA IS NULL THEN 1
|
|
ELSE NULL
|
|
END ) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_needareaString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_progetto)
|
|
ELSE NULL
|
|
END AS somma_controval_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_progetto)
|
|
ELSE NULL
|
|
END AS versato_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_progetto) > 0
|
|
THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS versato_need_progString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_progetto)
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN MONIT.MINUS_PLUSVALENZA IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_progetto) > 0
|
|
THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_need_progString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.VAR_PERC_PTF
|
|
ELSE NULL
|
|
END AS somma_var_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.COPERTURA
|
|
ELSE NULL
|
|
END AS somma_copertura_need_prog,
|
|
RISCHIO_AGGR_AREA.VAR_PERC_PTF AS var_needarea,
|
|
CASE
|
|
WHEN ISNULL(SUM(RISCHIO_AGGR_AREA.COPERTURA) OVER (PARTITION BY AREA.ID_AREA),0.00) = 0.00
|
|
THEN 'n.c.'
|
|
--------------------------------------------------------------
|
|
--INIZIO INTERVENTI OMNIA QUIII
|
|
WHEN @TOTSELFLIQ = @TOTLIQ and AREA.ID_AREA = 'LIQ'
|
|
THEN 'n.c.'
|
|
--FINE INTERVENTI OMNIA QUIII
|
|
--------------------------------------------------------------
|
|
ELSE NULL
|
|
END AS var_needareaString,
|
|
CAST(ISNULL(RISCHIO_AGGR_AREA.COPERTURA,100) AS DECIMAL (5,2)) AS copertura_needarea,
|
|
CASE
|
|
WHEN (ISNULL(RISCHIO_AGGR_AREA.copertura,100) < 100)
|
|
--THEN '(*) Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
ELSE ''
|
|
END AS copertura_needareaString,
|
|
RISCHIO_AGGR_PROG.VAR_PERC_PTF AS var_need_prog,
|
|
CASE
|
|
WHEN ISNULL(RISCHIO_AGGR_PROG.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS var_need_progString,
|
|
RISCHIO_AGGR_PROG.COPERTURA AS copertura_need_prog,
|
|
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) AS dataSottoscrizione,
|
|
PATRBF.ID_CONTRATTO AS ContrattoDossier,
|
|
--V
|
|
CASE WHEN PATRBF.TIPO_PRODOTTO <> 'CC' THEN ANAGPROD.DESCR_PRODOTTO
|
|
ELSE ANAGPROD.DESCR_PRODOTTO+' '+SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC))
|
|
END AS Descrizione,
|
|
--Bido to get Code_CC field
|
|
--NOT AT ALL ANAGPROD.DESCR_PRODOTTO + ' ' + (STUFF(substring(ANAG_CC.COD_CC , 4 , len(ANAG_CC.COD_CC) - 3), 3 , 0, '/') ) AS Descrizione,
|
|
--End Bido
|
|
--ANAGPROD.DESCR_PRODOTTO AS Descrizione,
|
|
PATRBF.CTV AS Controvalore,
|
|
PATRBF.CTV - MONIT.MINUS_PLUSVALENZA AS VersatoNetto,
|
|
CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 'n.d.' ELSE NULL END as VersatoNettoString,
|
|
MONIT.MINUS_PLUSVALENZA AS MinusPlusValenza,
|
|
CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 'n.d.' ELSE NULL END as MinusPlusValenzaString,
|
|
--V nuova gestione cc
|
|
case when (patrbf.tipo_prodotto ='CC') then 0.00
|
|
else RISCHIO_PROD.VAR_PERC_PTF
|
|
end AS VaRprodotto,
|
|
CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 and patrbf.tipo_prodotto <>'CC' THEN 'n.c.'
|
|
WHEN patrbf.isin in('EURO00000009', 'EURO10000007') THEN 'n.c.'
|
|
ELSE NULL END as varProdottoString,
|
|
case when (patrbf.tipo_prodotto ='CC') or (patrbf.isin in('EURO00000009', 'EURO10000007')) then 100.00
|
|
else RISCHIO_PROD.Copertura
|
|
end AS Coperturaprodotto,
|
|
PATRBF.REND_ANNO AS PerformanceYTD,
|
|
PATRBF.REND_SOTTOSCRIZIONE AS performanceDS,
|
|
PARTVIA_DISINV AS partitaViaggiante,
|
|
CASE WHEN (ISNULL(RISCHIO_AGGR_AREA.copertura,100) < 100) THEN '*' ELSE '' END AS nota_needarea,
|
|
RISCHIO_PROD.CREDITRISK as creditrisk,
|
|
CASE
|
|
WHEN ISNULL(RISCHIO_prod.COPERTURA,0.00) = 0.00 and patrbf.tipo_prodotto <> 'CC' THEN 'n.c.'
|
|
WHEN RISCHIO_PROD.CREDITRISK IS NULL THEN 'n.a.'
|
|
ELSE NULL
|
|
END as creditriskstring,
|
|
--CASE
|
|
-- WHEN RISCHIO_PROD.CREDITRISKEMIT IS NULL THEN 'n.a.'
|
|
-- ELSE cast(RISCHIO_PROD.CREDITRISKEMIT as varchar(20))
|
|
-- END as creditriskemit
|
|
RISCHIO_PROD.CREDITRISKEMIT as creditriskemit
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN
|
|
C6MartPeriodico.ANAG_PRODOTTI ANAGPROD
|
|
ON
|
|
ANAGPROD.COD_PRODOTTO = PATRBF.COD_PRODOTTO
|
|
INNER JOIN
|
|
C6MartPeriodico.AREA_BISOGNO AREA
|
|
ON
|
|
LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA
|
|
LEFT JOIN
|
|
C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_PROD
|
|
ON
|
|
PATRBF.RETE= RISCHIO_PROD.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
|
|
AND RISCHIO_PROD.COD_AGGREG =
|
|
CASE
|
|
WHEN (PATRBF.NOME_PROGETTO IS NOT NULL)
|
|
THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+
|
|
'|' + isnull(cast(patrbf.CHIAVE_progetto as varchar),'') + '|'
|
|
when LEFT(PATRBF.ID_AREA,3)='Na' then 'RISFIN|NA|'
|
|
ELSE 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
END
|
|
-- END + PATRBF.POSITION_ID
|
|
+ PATRBF.POSITION_ID
|
|
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_PROG
|
|
ON PATRBF.RETE= RISCHIO_AGGR_PROG.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_AGGR_PROG.COD_FISCALE
|
|
AND RISCHIO_AGGR_PROG.COD_AGGREG = 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO
|
|
+ '|' + isnull(cast(patrbf.CHIAVE_progetto as varchar),'')
|
|
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_AREA
|
|
ON PATRBF.RETE= RISCHIO_AGGR_AREA.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_AGGR_AREA.COD_FISCALE
|
|
AND RISCHIO_AGGR_AREA.COD_AGGREG = case
|
|
when LEFT(PATRBF.ID_AREA,3) = 'Na' then 'RISFIN|NA'
|
|
else 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3)
|
|
end
|
|
-- AND RISCHIO_AGGR_AREA.COD_AGGREG =
|
|
-- CASE AREA.ID_AREA
|
|
-- WHEN 'Na' THEN 'RISORSENONASSOCIATE'
|
|
-- ELSE 'COMPLESSIVO|BF|' + LEFT(PATRBF.ID_AREA,3)
|
|
-- END
|
|
LEFT JOIN
|
|
C6MartPeriodico.MONITORAGGIO_ATTUALE MONIT
|
|
ON
|
|
PATRBF.RETE = MONIT.RETE
|
|
AND PATRBF.COD_FISCALE = MONIT.COD_FISCALE
|
|
AND LEFT(PATRBF.ID_AREA, 3) = MONIT.ID_AREA
|
|
AND CASE WHEN PATRBF.NOME_PROGETTO IS NULL THEN 'XXX' ELSE PATRBF.NOME_PROGETTO END = MONIT.NOME_PROGETTO
|
|
And patrbf.ordinamento_progetto=monit.ordinamento_progetto
|
|
--Bido to get Code_CC field
|
|
LEFT OUTER JOIN C6MartPeriodico.ANAG_CC
|
|
ON
|
|
ANAG_CC.COD_FISCALE = PATRBF.COD_FISCALE
|
|
AND ANAG_CC.RETE = PATRBF.RETE
|
|
AND cast(ANAG_CC.cod_cc as integer ) = substring(PATRBF.position_id,72,9)
|
|
WHERE 1 = 1
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.CTV <>0
|
|
ORDER BY
|
|
AREASORTORDER,CHIAVE_progetto,nome_progetto, PATRBF.CTV DESC
|
|
END
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S162DettaglioProdotti
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S162DettaglioProdotti', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRTERZI.DESCR_GRUPPO_PRODOTTO AS TipoProdotto,
|
|
PATRTERZI.INTERMEDIARIO AS Intermediario,
|
|
dbo.TOSHORTDATESTRING(PATRTERZI.DATA_SOTTOSCRIZIONE) AS DataDiSottoscrizioni,
|
|
ANAGPRODTERZI.NOME_PRODOTTO AS Descrizione,
|
|
ROUND(PATRTERZI.CTV,2) AS Controvalore,
|
|
ROUND(PATRTERZI.VERSATO_NETTO,2) AS VersatoNetto,
|
|
ROUND(PATRTERZI.CTV,2) - ROUND(PATRTERZI.VERSATO_NETTO,2) AS MinusPlusValenza,
|
|
RISCHIOPROD.VAR_PERC_PTF AS VarPerc,
|
|
CASE
|
|
WHEN ISNULL(PATRTERZI.VERSATO_NETTO, 0) = 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS VersatoNettoString,
|
|
CASE
|
|
WHEN PATRTERZI.CTV - PATRTERZI.VERSATO_NETTO IS NULL THEN 'n.d.'
|
|
WHEN ISNULL(PATRTERZI.VERSATO_NETTO, 0) = 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS MinusPlusValenzaString,
|
|
CASE
|
|
WHEN ISNULL(RISCHIOPROD.copertura,0.00) = 0.00 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS VarPercString,
|
|
SUM(ROUND(PATRTERZI.CTV,2)) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) AS TotaleCTV,
|
|
SUM(ROUND(PATRTERZI.VERSATO_NETTO,2)) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) AS TotaleVersatoNetto,
|
|
CASE
|
|
WHEN COUNT(CASE WHEN PATRTERZI.VERSATO_NETTO IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) > 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS TotaleVersatoNettoString,
|
|
SUM(ROUND(PATRTERZI.CTV,2) - ROUND(PATRTERZI.VERSATO_NETTO,2)) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) AS TotaleMinus,
|
|
CASE
|
|
WHEN COUNT(CASE WHEN (PATRTERZI.CTV - PATRTERZI.VERSATO_NETTO) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) > 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS TotaleMinusString,
|
|
RISCHIOAGGREG.VAR_PERC_PTF AS TotaleVar,
|
|
CASE WHEN ISNULL(RISCHIOAGGREG.COPERTURA,0.00) = 0.00 THEN 'n.c.' ELSE NULL END AS TotaleVarString,
|
|
RISCHIOAGGREG.COPERTURA AS Copertura_Tot,
|
|
NULL AS Aggiornamento,
|
|
CASE
|
|
WHEN RISCHIOAGGREG.COPERTURA > 0 AND RISCHIOAGGREG.COPERTURA < 100
|
|
THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIOAGGREG.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
ELSE '' -- CASI 0, 100, NULL
|
|
END AS Copertura_Totale,
|
|
PATRTERZI.RETE AS Rete,
|
|
PATRTERZI.COD_FISCALE AS Cod_Fiscale,
|
|
'TABELLA PRODOTTI FINANZIARI ALTRI' AS Tipo,
|
|
CASE WHEN (ISNULL(RISCHIOAGGREG.copertura,100) < 100) THEN '*' ELSE '' END AS Note,
|
|
CASE WHEN PATRTERZI.COD_PRODOTTO = 'PRODOTTO NON IN CATALOGO' AND ISNULL(RISCHIOPROD.copertura,0.00) <> 0.00 THEN 1 ELSE 0 END AS ProdNonInCatalogo,
|
|
CASE UPPER(PATRTERZI.DESCR_GRUPPO_PRODOTTO)
|
|
WHEN 'FONDI' THEN 0
|
|
WHEN 'GESTIONI PATRIMONIALI' THEN 1
|
|
WHEN 'PRODOTTI DI TIPO AMMINISTRATO (ESCLUSO C/C)' THEN 2
|
|
WHEN 'ASSICURATIVI UNITI/INDEX LINKED' THEN 3
|
|
WHEN 'PORTAFOGLIO GENERICO' THEN 4
|
|
ELSE 10000
|
|
END AS OrdinamentoProdotti,
|
|
RISCHIOPROD.CREDITRISK as creditrisk,
|
|
CASE
|
|
WHEN ISNULL(RISCHIOpROD.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
WHEN RISCHIOPROD.CREDITRISK IS NULL THEN 'n.a.'
|
|
ELSE NULL
|
|
END as creditriskstring,
|
|
RISCHIOPROD.CREDITRISKEMIT as creditriskemit
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_TERZI PATRTERZI
|
|
INNER JOIN
|
|
C6MartPeriodico.ANAG_PRODOTTI_TERZI ANAGPRODTERZI
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO_TERZI = ANAGPRODTERZI.COD_PRODOTTO_TERZI
|
|
LEFT OUTER JOIN
|
|
--[10.10.32.85].C6StampeCentralizzate2.C6MartPeriodico.RISCHIO_AGGREGATO RISCHIOPROD
|
|
C6MartPeriodico.RISCHIO_AGGREGATO RISCHIOPROD
|
|
ON
|
|
PATRTERZI.RETE = RISCHIOPROD.RETE
|
|
AND PATRTERZI.COD_FISCALE = RISCHIOPROD.COD_FISCALE
|
|
AND RISCHIOPROD.COD_AGGREG ='TIPOPRODTERZI|TERZI|' + PATRTERZI.DESCR_GRUPPO_PRODOTTO + '|'+ PATRTERZI.COD_PRODOTTO_TERZI
|
|
LEFT OUTER JOIN
|
|
--[10.10.32.85].C6StampeCentralizzate2.C6MartPeriodico.RISCHIO_AGGREGATO RISCHIOAGGREG
|
|
C6MartPeriodico.RISCHIO_AGGREGATO RISCHIOAGGREG
|
|
ON
|
|
PATRTERZI.RETE = RISCHIOAGGREG.RETE
|
|
AND PATRTERZI.COD_FISCALE = RISCHIOAGGREG.COD_FISCALE
|
|
AND RISCHIOAGGREG.COD_AGGREG ='TIPOPRODTERZI|TERZI|' + PATRTERZI.DESCR_GRUPPO_PRODOTTO
|
|
WHERE 1 = 1
|
|
AND PATRTERZI.RETE = @Rete
|
|
AND PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
AND PATRTERZI.DESCR_GRUPPO_PRODOTTO IS NOT NULL
|
|
ORDER BY
|
|
OrdinamentoProdotti,
|
|
Controvalore DESC
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S163PatrimonioFinanziarioMAssetClass
|
|
SELECT DISTINCT '' as 'C6MartPeriodico.PL_D2_S163PatrimonioFinanziarioMAssetClass', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
ASSETCLASS.ORDINAMENTO AS Ordinamento,
|
|
VALORIASSET.ID_ASSETCLASS AS AssetClassId,
|
|
ASSETCLASS.DESCRIZIONE AS AssetClassName,
|
|
cast(SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) as decimal(19,6)) AS Controvalore,
|
|
(
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS) /
|
|
SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE')
|
|
) * 100 AS Percentuale,
|
|
cast (SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE') as decimal(19,6)) AS PatrimonioFinanziarioCTV
|
|
FROM (
|
|
--PRODOTTI TERZI A CATALOGO
|
|
SELECT
|
|
ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') AS ID_ASSETCLASS,
|
|
PATRTERZI.CTV * ISNULL(ASSETPERC.PERC,1) AS Controvalore
|
|
FROM
|
|
C6MART.PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
C6MART.ASSET_PERC ASSETPERC
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRTERZI.COD_PRODOTTO <> 'PRODOTTO NON IN CATALOGO'
|
|
AND PATRTERZI.RETE = @Rete
|
|
AND PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
UNION ALL
|
|
--PRODOTTI TERZI NON A CATALOGO
|
|
SELECT
|
|
ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') AS ID_ASSETCLASS,
|
|
PATRTERZI.CTV*ISNULL(ASSETPERC.PERC,1) AS Controvalore
|
|
FROM
|
|
C6MART.PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
C6MART.ASSET_PERC_TERZI ASSETPERC
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO_TERZI = ASSETPERC.COD_PRODOTTO_TERZI
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRTERZI.COD_PRODOTTO = 'PRODOTTO NON IN CATALOGO'
|
|
AND PATRTERZI.RETE = @Rete
|
|
AND PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
) VALORIASSET
|
|
INNER JOIN C6MART.ASSETCLASS ASSETCLASS
|
|
ON
|
|
ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
|
|
ORDER BY
|
|
ASSETCLASS.ORDINAMENTO
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S164PolizzeAltriIstituti
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S164PolizzeAltriIstituti', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRTERZI.INTERMEDIARIO AS Intermediario,
|
|
dbo.toShortDateString(PATRTERZI.DATA_SOTTOSCRIZIONE) AS DataDiSottoscrizione,
|
|
dbo.toShortDateString(PATRTERZI.DATA_SCADENZA) AS DataDiScadenza,
|
|
PATRTERZI.TIPO_POLIZZA AS TipologiaPolizza,
|
|
ANAGTERZI.NOME_PRODOTTO AS Descrizione,
|
|
NULL AS Aggiornamento,
|
|
PATRTERZI.CTV AS PrestazioneAssicurata,
|
|
PATRTERZI.TIPO_PRODOTTO AS TipoVersamento,
|
|
PATRTERZI.VERSATO_NETTO AS AmmontareVersamento
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_TERZI AS PATRTERZI
|
|
INNER JOIN
|
|
C6MartPeriodico.ANAG_PRODOTTI_TERZI AS ANAGTERZI
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO_TERZI = ANAGTERZI.COD_PRODOTTO_TERZI
|
|
AND ANAGTERZI.DESCR_PRODOTTO = 'Assets.FinancialAssets.PolizzeRivalutabili'
|
|
WHERE PATRTERZI.RETE = @Rete
|
|
AND PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S165FondiAltriIstituti
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S165FondiAltriIstituti', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRTERZI.INTERMEDIARIO AS Intermediario,
|
|
ANAGPRODTERZI.NOME_PRODOTTO AS descrizione_prodotto,
|
|
dbo.ToShortDateString(PATRTERZI.DATA_SOTTOSCRIZIONE) AS DataSottoscrizione,
|
|
PATRTERZI.FREQ_RATA as tipo_versamento,
|
|
PATRTERZI.CTV as controvalore,
|
|
PATRTERZI.VERSATO_NETTO as ammontare,
|
|
SUM(PATRTERZI.CTV) OVER (PARTITION BY 'TOTALE') as somma_controval,
|
|
RISCHIOPROD.VAR_PERC_PTF as Var_Prodotto,
|
|
CASE WHEN RISCHIOPROD.VAR_PERC_PTF IS NULL OR ISNULL(RISCHIOPROD.COPERTURA,0.00) = 0.00 THEN 'n.c.' ELSE NULL END as Var_ProdottoString,
|
|
RISCHIOAGGR.VAR_PERC_PTF as Var_Totale,
|
|
CASE WHEN RISCHIOAGGR.VAR_PERC_PTF IS NULL OR ISNULL(RISCHIOAGGR.COPERTURA,0.00) = 0.00 THEN 'n.c.' ELSE NULL END as Var_TotaleString,
|
|
--CASE WHEN (ISNULL(RISCHIOAGGR.COPERTURA,100) < 100) THEN '(*) Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIOAGGR.COPERTURA AS decimal (5,2)) AS varchar),'.',',') + '%' ELSE '' END AS copertura_totale,
|
|
CASE
|
|
WHEN RISCHIOAGGR.COPERTURA > 0 AND RISCHIOAGGR.COPERTURA < 100
|
|
THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIOAGGR.COPERTURA AS decimal (5,2)) AS varchar),'.',',') + '%'
|
|
ELSE '' -- CASI 0, 100, NULL
|
|
END AS copertura_totale,
|
|
CASE WHEN RISCHIOAGGR.COPERTURA > 0 AND RISCHIOAGGR.COPERTURA < 100 THEN '*' ELSE '' END as Nota,
|
|
CASE WHEN PATRTERZI.COD_PRODOTTO = 'PRODOTTO NON IN CATALOGO' THEN 1 ELSE 0 END AS ProdNonInCatalogo,
|
|
RISCHIOAGGR.CREDITRISK as creditrisk,
|
|
CASE
|
|
WHEN ISNULL(RISCHIOAGGR.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
WHEN RISCHIOAGGR.CREDITRISK IS NULL THEN 'n.a.'
|
|
ELSE NULL
|
|
END as creditriskstring
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_TERZI AS PATRTERZI
|
|
INNER JOIN
|
|
C6MartPeriodico.ANAG_PRODOTTI_TERZI AS ANAGPRODTERZI
|
|
ON
|
|
PATRTERZI.COD_PRODOTTO_TERZI = ANAGPRODTERZI.COD_PRODOTTO_TERZI
|
|
--AND ANAGPRODTERZI.ALBERO_PRODOTTI = 'Ass.Prev'
|
|
AND ANAGPRODTERZI.DESCR_PRODOTTO='Assets.FinancialAssets.ProdottiPrevidenziali'
|
|
LEFT OUTER JOIN
|
|
C6MartPeriodico.RISCHIO_AGGREGATO AS RISCHIOPROD
|
|
ON
|
|
PATRTERZI.RETE = RISCHIOPROD.RETE
|
|
AND PATRTERZI.COD_FISCALE = RISCHIOPROD.COD_FISCALE
|
|
AND RISCHIOPROD.COD_AGGREG = 'TIPOPRODTERZI|TERZI|' + ISNULL(DESCR_GRUPPO_PRODOTTO,PATRTERZI.TIPO_PRODOTTO) + '|' + PATRTERZI.cod_prodotto_terzi
|
|
LEFT OUTER JOIN
|
|
C6MartPeriodico.RISCHIO_AGGREGATO AS RISCHIOAGGR
|
|
ON
|
|
PATRTERZI.RETE = RISCHIOAGGR.RETE
|
|
AND PATRTERZI.COD_FISCALE = RISCHIOAGGR.COD_FISCALE
|
|
AND RISCHIOAGGR.COD_AGGREG = 'TIPOPRODTERZI|TERZI|' + ISNULL(DESCR_GRUPPO_PRODOTTO,PATRTERZI.TIPO_PRODOTTO)
|
|
WHERE
|
|
PATRTERZI.rete = @Rete
|
|
and PATRTERZI.cod_fiscale = @CodiceFiscale
|
|
--AND PATRTERZI.TIPO_PRODOTTO = 'Fondi pensione'
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S166ContoCorrenteAI
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S166ContoCorrenteAI', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
CCTerzi.INTERMEDIARIO AS Intermediario,
|
|
CCTerzi.COD_CC AS ContoCorrente,
|
|
CCTerzi.SALDO AS Saldo,
|
|
null as creditrisk,
|
|
'n.a.' as creditriskstring,
|
|
0.00 as var
|
|
FROM
|
|
C6MartPeriodico.ANAG_CC_Terzi CCTerzi
|
|
WHERE
|
|
CCTerzi.rete= @Rete
|
|
AND CCTerzi.cod_fiscale= @CodiceFiscale
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S167DistribuzioneRischioCredito
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S167DistribuzioneRischioCredito', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, RETE,
|
|
COD_FISCALE,
|
|
INTERMEDIARIO,
|
|
CASE
|
|
WHEN INTERMEDIARIO IN ('Banca Fideuram',
|
|
'Sanpaolo Invest') THEN 1
|
|
ELSE 2
|
|
END AS ORDINE,
|
|
SUM(CTV) AS CTV_TOT,
|
|
SUM(CASE WHEN RISKCLASS = 'NON COPERTO' THEN CTV ELSE 0 END) AS CTV_notCovered,
|
|
SUM(CASE WHEN RISKCLASS = 'Classe A'
|
|
OR RISKCLASS = 'Classe B'
|
|
OR RISKCLASS = 'Classe C'
|
|
OR RISKCLASS = 'Classe D' THEN CTV ELSE 0 END) AS CTV_CreditRisk,
|
|
SUM(CASE WHEN RISKCLASS = 'NA' THEN CTV ELSE 0 END) AS CTV_na,
|
|
--CASE WHEN SUM(CTV)=0 THEN 0 ELSE (SUM(CASE WHEN RISKCLASS = 'NON COPERTO' THEN CTV ELSE 0 END)/SUM(CTV))*100 END AS PERC_notCovered,
|
|
CASE
|
|
WHEN SUM(CTV)=0 THEN 0
|
|
ELSE (SUM(CASE WHEN RISKCLASS = 'Classe A'
|
|
OR RISKCLASS = 'Classe B'
|
|
OR RISKCLASS = 'Classe C'
|
|
OR RISKCLASS = 'Classe D' THEN CTV ELSE 0 END) /SUM(CASE WHEN RISKCLASS <> 'NON COPERTO' THEN CTV ELSE 1 END))*100
|
|
END AS PERC_CreditRisk,
|
|
CASE
|
|
WHEN SUM(CTV)=0 THEN 0
|
|
ELSE (SUM(CASE WHEN RISKCLASS = 'NA' THEN CTV ELSE 0 END) /sum(CASE WHEN RISKCLASS <> 'NON COPERTO' THEN CTV ELSE 1 END))*100
|
|
END AS PERC_NA
|
|
FROM ( -- Start 1.1
|
|
SELECT PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIO_PROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END AS RISKCLASS,
|
|
CASE
|
|
WHEN PATRBF.RETE = 'F' THEN 'Banca Fideuram'
|
|
ELSE 'Sanpaolo Invest'
|
|
END AS INTERMEDIARIO,
|
|
--SUM(CASE WHEN PATRBF.ID_AREA = 'Cc' AND PATRBF.CTV < 0 THEN 0 ELSE PATRBF.CTV END) AS CTV
|
|
SUM(CASE WHEN ctvcc.cc_ctv <0
|
|
AND patrbf.id_area IN ('cc','self') THEN 0 ELSE PATRBF.CTV END) AS ctv
|
|
FROM [C6Martperiodico].PATRIMONIO_BF PATRBF ---Per calcolare la somma algebrica dei cc
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS cc_ctv
|
|
FROM [C6MARTPERIODICO].pATRIMONIO_BF
|
|
WHERE id_Area IN ('Cc',
|
|
'Self')
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvcc ON patrbf.cod_fiscale=ctvcc.cod_fiscale
|
|
AND patrbf.rete=ctvcc.rete --fine calcolo somma cc
|
|
LEFT OUTER JOIN [C6Martperiodico].RISCHIO_AGGREGATO RISCHIO_PROD ON PATRBF.RETE= RISCHIO_PROD.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
|
|
AND RISCHIO_PROD.COD_AGGREG = CASE
|
|
WHEN (PATRBF.NOME_PROGETTO IS NOT NULL) THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO + '|' + isnull(cast(PATRBF.ordinamento_progetto AS varchar),'') + '|' --V
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('EXT',
|
|
'INV',
|
|
'PRE',
|
|
'RIS',
|
|
'LIQ') THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('CC') THEN 'COMPLESSIVO|BF|CC|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('SELF') THEN 'COMPLESSIVO|BF|SELF|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('NA') THEN 'RISFIN|' + LEFT(PATRBF.ID_AREA,3) + '|' --
|
|
END + PATRBF.POSITION_ID --INNER JOIN C6MartPeriodico.CODIFICA_CREDITRISK CR
|
|
LEFT OUTER JOIN C6MartPeriodico.CODIFICA_CREDITRISK CR ON RISCHIO_PROD.CREDITRISK = cr.profilo --V
|
|
WHERE PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.RETE = @Rete --
|
|
GROUP BY PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIO_PROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END,
|
|
CASE
|
|
WHEN PATRBF.RETE = 'F' THEN 'FIDEURAM'
|
|
ELSE 'SANPAOLO'
|
|
END --- End 1.1
|
|
UNION ALL -- Start 1.2
|
|
SELECT PATRTERZI.RETE,
|
|
PATRTERZI.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIOPROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END AS RISKCLASS,
|
|
'Altri Istituti' AS INTERMEDIARIO,
|
|
--PATRTERZI.INTERMEDIARIO,
|
|
SUM(ROUND(CTV,2)) AS CTV
|
|
FROM [C6Martperiodico].PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS cc_ctv
|
|
FROM [C6Martperiodico].pATRIMONIO_terzi
|
|
WHERE tipo_prodotto='Conti correnti'
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvcc ON patrterzi.cod_fiscale=ctvcc.cod_fiscale
|
|
AND patrterzi.rete=ctvcc.rete
|
|
LEFT OUTER JOIN [C6Martperiodico].RISCHIO_AGGREGATO RISCHIOPROD ON PATRTERZI.RETE = RISCHIOPROD.RETE
|
|
AND PATRTERZI.COD_FISCALE = RISCHIOPROD.COD_FISCALE
|
|
AND RISCHIOPROD.COD_AGGREG ='TIPOPRODTERZI|TERZI|' +ISNULL(PATRTERZI.DESCR_GRUPPO_PRODOTTO, PATRTERZI.TIPO_PRODOTTO) + '|'+ PATRTERZI.COD_PRODOTTO_TERZI
|
|
LEFT OUTER JOIN [C6Martperiodico].CODIFICA_CREDITRISK CR ON RISCHIOPROD.CREDITRISK = cr.profilo --V
|
|
WHERE PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
AND PATRTERZI.RETE = @Rete --
|
|
GROUP BY PATRTERZI.RETE,
|
|
PATRTERZI.COD_FISCALE,
|
|
--PATRTERZI.INTERMEDIARIO,
|
|
CASE
|
|
WHEN RISCHIOPROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END -- End 1.2
|
|
)H
|
|
GROUP BY RETE,
|
|
COD_FISCALE,
|
|
INTERMEDIARIO --------
|
|
UNION ALL -----------
|
|
--QUERY TOTALE
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S167DistribuzioneRischioCredito', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, RETE,
|
|
COD_FISCALE,
|
|
'Complessivo' AS INTERMEDIARIO,
|
|
3 AS ORDINE,
|
|
SUM(CTV) AS CTV_TOT,
|
|
SUM(CASE WHEN RISKCLASS = 'NON COPERTO' THEN CTV ELSE 0 END) AS CTV_notCovered,
|
|
SUM(CASE WHEN RISKCLASS = 'Classe A'
|
|
OR RISKCLASS = 'Classe B'
|
|
OR RISKCLASS = 'Classe C'
|
|
OR RISKCLASS = 'Classe D' THEN CTV ELSE 0 END) AS CTV_CreditRisk,
|
|
SUM(CASE WHEN RISKCLASS ='NA' THEN CTV ELSE 0 END) AS CTV_na,
|
|
--CASE WHEN SUM(CTV)=0 THEN 0 ELSE (SUM(CASE WHEN RISKCLASS = 'NON COPERTO' THEN CTV ELSE 0 END)/SUM(CTV))*100 END AS PERC_notCovered,
|
|
CASE
|
|
WHEN SUM(CTV)=0 THEN 0
|
|
ELSE (SUM(CASE WHEN RISKCLASS = 'Classe A'
|
|
OR RISKCLASS = 'Classe B'
|
|
OR RISKCLASS = 'Classe C'
|
|
OR RISKCLASS = 'Classe D' THEN CTV ELSE 0 END) /SUM(CASE WHEN RISKCLASS <> 'NON COPERTO' THEN CTV ELSE 1 END))*100
|
|
END AS PERC_CreditRisk,
|
|
CASE
|
|
WHEN SUM(CTV)=0 THEN 0
|
|
ELSE (SUM(CASE WHEN RISKCLASS = 'NA' THEN CTV ELSE 0 END) /sum(CASE WHEN RISKCLASS <> 'NON COPERTO' THEN CTV ELSE 1 END))*100
|
|
END AS PERC_NA
|
|
FROM (--- Start 2.1
|
|
SELECT PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIO_PROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END AS RISKCLASS,
|
|
--SUM(CASE WHEN PATRBF.ID_AREA = 'Cc' AND PATRBF.CTV < 0 THEN 0 ELSE PATRBF.CTV END) AS CTV
|
|
SUM(CASE WHEN ctvcc.cc_ctv <0
|
|
AND patrbf.id_area IN ('cc','self') THEN 0 ELSE PATRBF.CTV END) AS CTV
|
|
FROM [C6Martperiodico].PATRIMONIO_BF PATRBF --per fare la somma algebrica dei cc
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS cc_ctv
|
|
FROM [C6MARTPERIODICO].pATRIMONIO_BF
|
|
WHERE id_Area IN ('Cc',
|
|
'Self')
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvcc ON patrbf.cod_fiscale=ctvcc.cod_fiscale
|
|
AND patrbf.rete=ctvcc.rete --fine somma cc
|
|
LEFT OUTER JOIN [C6Martperiodico].RISCHIO_AGGREGATO RISCHIO_PROD ON PATRBF.RETE= RISCHIO_PROD.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
|
|
AND RISCHIO_PROD.COD_AGGREG = CASE
|
|
WHEN (PATRBF.NOME_PROGETTO IS NOT NULL) THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+ '|' + isnull(cast(PATRBF.ordinamento_progetto AS varchar),'')+'|' --V
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('EXT',
|
|
'INV',
|
|
'PRE',
|
|
'RIS',
|
|
'LIQ') THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('CC') THEN 'COMPLESSIVO|BF|CC|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('SELF') THEN 'COMPLESSIVO|BF|SELF|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('NA') THEN 'RISFIN|' + LEFT(PATRBF.ID_AREA,3) + '|' --
|
|
END + PATRBF.POSITION_ID
|
|
LEFT OUTER JOIN [C6Martperiodico].CODIFICA_CREDITRISK CR ON RISCHIO_PROD.CREDITRISK = cr.profilo --V
|
|
WHERE PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.RETE = @Rete --
|
|
GROUP BY PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIO_PROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END -- End 2.1
|
|
UNION ALL -- Start 2.2
|
|
SELECT PATRTERZI.RETE,
|
|
PATRTERZI.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIOPROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END AS RISKCLASS,
|
|
SUM(ROUND(CTV,2)) AS CTV
|
|
FROM [C6Martperiodico].PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS cc_ctv
|
|
FROM [C6Martperiodico].pATRIMONIO_terzi
|
|
WHERE tipo_prodotto='Conti correnti'
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvcc ON patrterzi.cod_fiscale=ctvcc.cod_fiscale
|
|
AND patrterzi.rete=ctvcc.rete
|
|
LEFT OUTER JOIN [C6Martperiodico].RISCHIO_AGGREGATO RISCHIOPROD ON PATRTERZI.RETE = RISCHIOPROD.RETE
|
|
AND PATRTERZI.COD_FISCALE = RISCHIOPROD.COD_FISCALE
|
|
AND RISCHIOPROD.COD_AGGREG ='TIPOPRODTERZI|TERZI|' + ISNULL(PATRTERZI.DESCR_GRUPPO_PRODOTTO, PATRTERZI.TIPO_PRODOTTO) + '|'+ PATRTERZI.COD_PRODOTTO_TERZI
|
|
LEFT OUTER JOIN [C6Martperiodico].CODIFICA_CREDITRISK CR ON RISCHIOPROD.CREDITRISK = cr.profilo --V
|
|
WHERE PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
AND PATRTERZI.RETE = @Rete --
|
|
GROUP BY PATRTERZI.RETE,
|
|
PATRTERZI.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIOPROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END --- End 2.2
|
|
)H
|
|
GROUP BY RETE,
|
|
COD_FISCALE
|
|
--------------------------------------------------------------------
|
|
----------------------------------------------- C6MartPeriodico.PL_D2_S168TabellaEmittenti
|
|
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 '' as 'C6MartPeriodico.PL_D2_S168TabellaEmittenti', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
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
|
|
--------------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S43DettaglioProdottiBF
|
|
BEGIN
|
|
--INIZIO INTERVENTI OMNIA QUIII
|
|
DECLARE @TOTSELFLIQ2 AS INT
|
|
SELECT @TOTSELFLIQ2 = COUNT(*)
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF
|
|
WHERE PATRIMONIO_BF.RETE = @Rete
|
|
AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
|
|
AND ISIN in('EURO00000009', 'EURO10000007')
|
|
AND ID_AREA = 'LIQ'
|
|
DECLARE @TOTLIQ2 AS INT
|
|
SELECT @TOTLIQ2 = COUNT(*)
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF
|
|
WHERE PATRIMONIO_BF.RETE = @Rete
|
|
AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
|
|
AND ID_AREA = 'LIQ'
|
|
--FINE INTERVENTI OMNIA QUIII
|
|
--------------------------------------------------------------------------------------
|
|
SELECT '' as 'C6MartPeriodico.PL_S43DettaglioProdottiBF', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Pre'
|
|
ELSE AREA.ID_AREA
|
|
END AS needarea,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Previdenza'
|
|
WHEN AREA.ID_AREA = 'Na' THEN 'Non allocate'
|
|
ELSE AREA.NOME_AREA
|
|
END AS needareades,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 3
|
|
ELSE AREA.ORDINAMENTO
|
|
END AS areasortorder,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND PATRBF.NOME_PROGETTO IS NULL THEN 'Prodotti non associati a progetti'
|
|
ELSE ltrim(rtrim(PATRBF.NOME_PROGETTO))
|
|
END AS nome_progetto,
|
|
PATRBF.CHIAVE_PROGETTO AS ORDINAMENTO_PROGETTO,
|
|
SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA) AS somma_controval_needarea,
|
|
SUM(PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA) AS versato_needarea,
|
|
CASE
|
|
WHEN COUNT(CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END as versato_needareaString,
|
|
SUM(MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA) AS MinusPlusValenza_needarea,
|
|
CASE
|
|
WHEN COUNT( CASE
|
|
WHEN MONIT.MINUS_PLUSVALENZA IS NULL THEN 1
|
|
ELSE NULL
|
|
END ) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_needareaString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO)
|
|
ELSE NULL
|
|
END AS somma_controval_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO)
|
|
ELSE NULL
|
|
END AS versato_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO) > 0
|
|
THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS versato_need_progString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO)
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN MONIT.MINUS_PLUSVALENZA IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO) > 0
|
|
THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_need_progString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.VAR_PERC_PTF
|
|
ELSE NULL
|
|
END AS somma_var_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.COPERTURA
|
|
ELSE NULL
|
|
END AS somma_copertura_need_prog,
|
|
RISCHIO_AGGR_AREA.VAR_PERC_PTF AS var_needarea,
|
|
CASE
|
|
WHEN ISNULL(SUM(RISCHIO_AGGR_AREA.COPERTURA) OVER (PARTITION BY AREA.ID_AREA),0.00) = 0.00
|
|
THEN 'n.c.'
|
|
--------------------------------------------------------------
|
|
--INIZIO INTERVENTI OMNIA QUIII
|
|
WHEN @TOTSELFLIQ2 = @TOTLIQ2 and AREA.ID_AREA = 'LIQ'
|
|
THEN 'n.c.'
|
|
--FINE INTERVENTI OMNIA QUIII
|
|
--------------------------------------------------------------
|
|
ELSE NULL
|
|
END AS var_needareaString,
|
|
CAST(ISNULL(RISCHIO_AGGR_AREA.COPERTURA,100) AS DECIMAL (5,2)) AS copertura_needarea,
|
|
CASE
|
|
WHEN (ISNULL(RISCHIO_AGGR_AREA.copertura,100) < 100)
|
|
--THEN '(*) Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
ELSE ''
|
|
END AS copertura_needareaString,
|
|
RISCHIO_AGGR_PROG.VAR_PERC_PTF AS var_need_prog,
|
|
CASE
|
|
WHEN ISNULL(RISCHIO_AGGR_PROG.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS var_need_progString,
|
|
RISCHIO_AGGR_PROG.COPERTURA AS copertura_need_prog,
|
|
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) AS dataSottoscrizione,
|
|
PATRBF.ID_CONTRATTO AS ContrattoDossier,
|
|
--V
|
|
CASE WHEN PATRBF.TIPO_PRODOTTO <> 'CC' THEN ANAGPROD.DESCR_PRODOTTO
|
|
ELSE ANAGPROD.DESCR_PRODOTTO+' '+SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC))
|
|
END AS Descrizione,
|
|
--Bido to get Code_CC field
|
|
--NOT AT ALL ANAGPROD.DESCR_PRODOTTO + ' ' + (STUFF(substring(ANAG_CC.COD_CC , 4 , len(ANAG_CC.COD_CC) - 3), 3 , 0, '/') ) AS Descrizione,
|
|
--End Bido
|
|
--ANAGPROD.DESCR_PRODOTTO AS Descrizione,
|
|
PATRBF.CTV AS Controvalore,
|
|
PATRBF.CTV - MONIT.MINUS_PLUSVALENZA AS VersatoNetto,
|
|
CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 'n.d.' ELSE NULL END as VersatoNettoString,
|
|
MONIT.MINUS_PLUSVALENZA AS MinusPlusValenza,
|
|
CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 'n.d.' ELSE NULL END as MinusPlusValenzaString,
|
|
--V nuova gestione cc
|
|
case when (patrbf.tipo_prodotto ='CC') then 0.00
|
|
else RISCHIO_PROD.VAR_PERC_PTF
|
|
end AS VaRprodotto,
|
|
--INIZIO INTERVENTI OMNIA
|
|
--CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 and patrbf.tipo_prodotto <>'CC' THEN 'n.c.' ELSE NULL END as varProdottoString,
|
|
CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 and patrbf.tipo_prodotto <>'CC' THEN 'n.c.'
|
|
WHEN patrbf.isin in('EURO00000009', 'EURO10000007') THEN 'n.c.'
|
|
ELSE NULL END as varProdottoString,
|
|
--FINE INTERVENTI OMNIA
|
|
--INIZIO INTERVENTI OMNIA
|
|
--case when (patrbf.tipo_prodotto ='CC') then 100.00
|
|
case when (patrbf.tipo_prodotto ='CC') or (patrbf.isin in('EURO00000009', 'EURO10000007')) then 100.00
|
|
--FINE INTERVENTI OMNIA
|
|
else RISCHIO_PROD.Copertura
|
|
end AS Coperturaprodotto,
|
|
PATRBF.REND_ANNO AS PerformanceYTD,
|
|
PATRBF.REND_SOTTOSCRIZIONE AS performanceDS,
|
|
PARTVIA_DISINV AS partitaViaggiante,
|
|
CASE WHEN (ISNULL(RISCHIO_AGGR_AREA.copertura,100) < 100) THEN '*' ELSE '' END AS nota_needarea,
|
|
RISCHIO_PROD.CREDITRISK as creditrisk,
|
|
CASE
|
|
--INIZIO INTERVENTI OMNIA
|
|
-- WHEN ISNULL(RISCHIO_prod.COPERTURA,0.00) = 0.00 and patrbf.tipo_prodotto <> 'CC' THEN 'n.c.'
|
|
WHEN (ISNULL(RISCHIO_prod.COPERTURA,0.00) = 0.00
|
|
and patrbf.tipo_prodotto <> 'CC')
|
|
or (patrbf.isin in('EURO00000009', 'EURO10000007'))
|
|
THEN 'n.c.'
|
|
--FINE INTERVENTI OMNIA
|
|
WHEN RISCHIO_PROD.CREDITRISK IS NULL THEN 'n.a.'
|
|
ELSE NULL
|
|
END as creditriskstring,
|
|
--CASE
|
|
-- WHEN RISCHIO_PROD.CREDITRISKEMIT IS NULL THEN 'n.a.'
|
|
-- ELSE cast(RISCHIO_PROD.CREDITRISKEMIT as varchar(20))
|
|
-- END as creditriskemit
|
|
RISCHIO_PROD.CREDITRISKEMIT as creditriskemit
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN
|
|
C6MartPeriodico.ANAG_PRODOTTI ANAGPROD
|
|
ON
|
|
ANAGPROD.COD_PRODOTTO = PATRBF.COD_PRODOTTO
|
|
INNER JOIN
|
|
C6MartPeriodico.AREA_BISOGNO AREA
|
|
ON
|
|
--LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA
|
|
(LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA and AREA.ID_AREA <>'self') or (PATRBF.ID_AREA='self' and AREA.ID_AREA ='self')
|
|
LEFT JOIN
|
|
C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_PROD
|
|
ON
|
|
PATRBF.RETE= RISCHIO_PROD.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
|
|
AND RISCHIO_PROD.COD_AGGREG =
|
|
CASE
|
|
WHEN (PATRBF.NOME_PROGETTO IS NOT NULL)
|
|
THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+
|
|
'|' + isnull(cast(patrbf.CHIAVE_PROGETTO as varchar),'') + '|'
|
|
when LEFT(PATRBF.ID_AREA,3)='Na' then 'RISFIN|NA|'
|
|
ELSE 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
END
|
|
-- END + PATRBF.POSITION_ID
|
|
+ PATRBF.POSITION_ID
|
|
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_PROG
|
|
ON PATRBF.RETE= RISCHIO_AGGR_PROG.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_AGGR_PROG.COD_FISCALE
|
|
AND RISCHIO_AGGR_PROG.COD_AGGREG = 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO
|
|
+ '|' + isnull(cast(patrbf.CHIAVE_PROGETTO as varchar),'')
|
|
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_AREA
|
|
ON PATRBF.RETE= RISCHIO_AGGR_AREA.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_AGGR_AREA.COD_FISCALE
|
|
AND RISCHIO_AGGR_AREA.COD_AGGREG = case
|
|
when LEFT(PATRBF.ID_AREA,3) = 'Na' then 'RISFIN|NA'
|
|
else 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3)
|
|
end
|
|
-- AND RISCHIO_AGGR_AREA.COD_AGGREG =
|
|
-- CASE AREA.ID_AREA
|
|
-- WHEN 'Na' THEN 'RISORSENONASSOCIATE'
|
|
-- ELSE 'COMPLESSIVO|BF|' + LEFT(PATRBF.ID_AREA,3)
|
|
-- END
|
|
LEFT JOIN
|
|
C6MartPeriodico.MONITORAGGIO_ATTUALE MONIT
|
|
ON
|
|
PATRBF.RETE = MONIT.RETE
|
|
AND PATRBF.COD_FISCALE = MONIT.COD_FISCALE
|
|
AND LEFT(PATRBF.ID_AREA, 3) = MONIT.ID_AREA
|
|
AND CASE WHEN PATRBF.NOME_PROGETTO IS NULL THEN 'XXX' ELSE PATRBF.NOME_PROGETTO END = MONIT.NOME_PROGETTO
|
|
And patrbf.ordinamento_progetto=monit.ordinamento_progetto
|
|
--Bido to get Code_CC field
|
|
LEFT OUTER JOIN C6MartPeriodico.ANAG_CC
|
|
ON
|
|
ANAG_CC.COD_FISCALE = PATRBF.COD_FISCALE
|
|
AND ANAG_CC.RETE = PATRBF.RETE
|
|
AND cast(ANAG_CC.cod_cc as integer ) = substring(PATRBF.position_id,72,9)
|
|
WHERE 1 = 1
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.CTV <>0
|
|
ORDER BY
|
|
AREASORTORDER,CHIAVE_PROGETTO,nome_progetto, PATRBF.CTV DESC
|
|
END
|
|
--select top 1 * from c6martperiodico.patrimonio_bf where tipo_prodotto = 'CC'
|
|
--select SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC)),ANAG_CC.COD_CC
|
|
--from c6martperiodico.anag_cc
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_D2_S169RischiomercatoRischiocredito
|
|
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S169RischiomercatoRischiocredito', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, 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
|
|
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,
|
|
--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.cod_fiscale = @codiceFiscale
|
|
AND PATRBF.rete= @rete
|
|
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 Banca Fideuram'
|
|
ELSE 'Patrimonio Sanpaolo Invest'
|
|
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.cod_fiscale =@codiceFiscale
|
|
AND TERZI.rete=@rete
|
|
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 Banca Fideuram'
|
|
WHEN RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF'
|
|
AND PATRBF.RETE= 'S' THEN 'Patrimonio Sanpaolo Invest'
|
|
ELSE 'Patrimonio altri Istituti'
|
|
END)= PATRBF.INSTITUTENAME
|
|
WHERE ( RISK_AGG.COD_AGGREG= 'COMPLESSIVO|BF'
|
|
OR RISK_AGG.COD_AGGREG= 'COMPLESSIVO|TERZI' )
|
|
UNION ALL
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S169RischiomercatoRischiocredito', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, 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 cod_fiscale =@codiceFiscale
|
|
AND rete=@rete
|
|
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 cod_fiscale =@codiceFiscale
|
|
AND rete=@rete
|
|
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 END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_D2_S170RischioDiversificazione
|
|
BEGIN
|
|
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
|
|
declare @ctvbf as decimal(12,2)
|
|
SELECT @ctvbf = isnull(sum(ctv),0) FROM c6martperiodico.patrimonio_bf
|
|
where cod_fiscale = @CodiceFiscale
|
|
and rete= @Rete
|
|
and CTV > 0
|
|
declare @ctvterzi as decimal (12,2)
|
|
SELECT @ctvterzi = isnull(sum(ctv),0) FROM c6martperiodico.patrimonio_terzi
|
|
where cod_fiscale = @CodiceFiscale
|
|
and rete= @Rete
|
|
and CTV > 0
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S170RischioDiversificazione', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
K.COD_FISCALE,
|
|
K.RETE,
|
|
CASE
|
|
when cod_Aggreg = 'COMPLESSIVO|BF' then @ctvbf
|
|
when cod_Aggreg = 'COMPLESSIVO|TERZI' then @ctvterzi
|
|
when cod_Aggreg = 'COMPLESSIVO' then @ctvbf + @ctvterzi
|
|
else 0
|
|
END AS CTV_AGGREG,
|
|
K.CREDITRISK,
|
|
CASE
|
|
WHEN (@contaPOS - @contacc = 0) THEN 0
|
|
ELSE VAR_PERC_PTF
|
|
END AS VAR,
|
|
CASE WHEN ISNULL(COPERTURA,0.00) = 0.00 AND @contaPOS - @contacc <> 0 THEN 'n.c.' ELSE NULL END as varString,
|
|
CASE
|
|
WHEN (ISNULL(copertura,100) < 100) AND @contaPOS - @contacc <> 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,
|
|
beneficio as diversificazione,
|
|
CASE
|
|
WHEN (@contaPOS - @contacc = 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 'Banca 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,
|
|
b.max_var as var_profilo,
|
|
--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
|
|
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')
|
|
AND k.COD_FISCALE = @COdiceFISCALE
|
|
AND k.RETE= @RETE
|
|
ORDER BY ordine
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_D2_S171PatrimonioNonRappresentabileAssettClass
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
DECLARE @NOMERETE VARCHAR(20)
|
|
IF @RETE = 'F'
|
|
SET @NOMERETE = 'Banca Fideuram'
|
|
ELSE
|
|
SET @NOMERETE = 'Sanpaolo Invest'
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S171PatrimonioNonRappresentabileAssettClass', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRBF.ID_CONTRATTO as Contratto,
|
|
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) as DataSottoscrizione,
|
|
ANAGP.DESCR_PRODOTTO AS Descrizione,
|
|
PATRBF.CTV as Controvalore,
|
|
PATRBF.CTV as controvalore_contratto,
|
|
PATRBF.PARTVIA_DISINV as PartitaViaggiante,
|
|
@NOMERETE as Intermediario
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF PATRBF
|
|
INNER JOIN C6MARTPERIODICO.ANAG_PRODOTTI ANAGP
|
|
ON PATRBF.COD_PRODOTTO = ANAGP.COD_PRODOTTO
|
|
LEFT OUTER JOIN C6MARTPERIODICO.ASSET_PERC ASSETPERC
|
|
ON PATRBF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.TIPO_PRODOTTO <> 'ASUL'
|
|
AND ASSETPERC.COD_PRODOTTO IS NULL
|
|
--INIZIO INTERVENTI OMNIA
|
|
AND PATRBF.CTV <> 0.00
|
|
--INIZIO INTERVENTI OMNIA
|
|
UNION ALL
|
|
SELECT '' as 'C6MartPeriodico.PL_D2_S171PatrimonioNonRappresentabileAssettClass', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRBF.ID_CONTRATTO as Contratto,
|
|
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) as DataSottoscrizione,
|
|
ANAGP.DESCR_PRODOTTO AS Descrizione,
|
|
SUM(ASUL.CTV) as Controvalore,
|
|
MAX(PATRBF.CTV) as controvalore_contratto,
|
|
SUM(PATRBF.PARTVIA_DISINV) as PartitaViaggiante,
|
|
@NOMERETE as Intermediario
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF PATRBF
|
|
INNER JOIN C6MARTPERIODICO.ANAG_PRODOTTI ANAGP
|
|
ON PATRBF.COD_PRODOTTO = ANAGP.COD_PRODOTTO
|
|
INNER JOIN C6MARTPERIODICO.DETTAGLIO_ASUL ASUL
|
|
ON PATRBF.RETE = ASUL.RETE
|
|
AND PATRBF.COD_FISCALE = ASUL.COD_FISCALE
|
|
AND PATRBF.ID_CONTRATTO = ASUL.ID_CONTRATTO
|
|
LEFT OUTER JOIN C6MARTPERIODICO.ASSET_PERC ASSETPERC
|
|
ON ASSETPERC.COD_PRODOTTO = ASUL.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.TIPO_PRODOTTO = 'ASUL'
|
|
AND ASSETPERC.COD_PRODOTTO IS NULL
|
|
GROUP BY
|
|
PATRBF.ID_CONTRATTO,
|
|
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) ,
|
|
ANAGP.DESCR_PRODOTTO
|
|
ORDER BY CONTROVALORE DESC
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S25Anagrafica
|
|
BEGIN
|
|
SELECT '' as 'C6MartPeriodico.PL_S25Anagrafica', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, [Cliente]
|
|
,[DatadiNascita]
|
|
,[ETA]
|
|
,[Professione]
|
|
,[StatoCivile]
|
|
,[NucleoFamiliare]
|
|
,[Residenza]
|
|
,[CodiceFiscale]
|
|
,[CodicePB]
|
|
,[CodiceRete]
|
|
,[CodiceContratto]
|
|
,[StatoContratto]
|
|
,[DataSottoscrizione]
|
|
,[DataPerfezionamento]
|
|
,[FasciaCPP]
|
|
,[IndirizzoEmail]
|
|
,[ProduzioneReport]
|
|
,[Indirizzo]
|
|
,[CAP]
|
|
,[Citta]
|
|
,[Provincia]
|
|
,[Nazione]
|
|
,[PRESSO]
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[TB_S25Anagrafica]
|
|
WHERE [CodiceRete] = @Rete
|
|
AND [CodiceFiscale] = @CodiceFiscale
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_AreeMonitorate
|
|
-- 2resultsets
|
|
BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
--marianna controllare date di uscita coerenti tra tcptab e tanab, nel caso forzare la chiusura del monitoraggio nella tanab
|
|
--exec C6MartPeriodico.SRV_LISTA_DISALLINEAMENTI_MONIT
|
|
SELECT '' as 'C6MartPeriodico.PL_AreeMonitorate', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, max(convert(datetime,convert(varchar(8),dtuscita))) AS dtfine,
|
|
rete,
|
|
codarea,
|
|
codfis,
|
|
codprog INTO #tcptab_aggr_dt
|
|
FROM c6stagingperiodico.tcptab
|
|
WHERE ctvatt>0
|
|
GROUP BY rete,
|
|
codfis,
|
|
codarea,
|
|
codprog
|
|
SELECT '' as 'C6MartPeriodico.PL_AreeMonitorate', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, a.codfis,
|
|
a.rete,
|
|
a.codarea,
|
|
a.codprog,
|
|
a.dtfine tanab_dt,
|
|
b.dtfine tcptab_dt INTO #dis_dt
|
|
FROM c6stagingperiodico.tanab a
|
|
FULL JOIN #tcptab_aggr_dt b ON a.rete = b.rete
|
|
AND a.codfis = b.codfis
|
|
AND a.codarea = b.codarea
|
|
AND a.codprog = b.codprog
|
|
WHERE convert(datetime,convert(varchar(8),a.dtfine))<>b.dtfine
|
|
AND b.dtfine<C6MartPeriodico.getDataCongelamento()
|
|
AND a.codfis NOT IN
|
|
(SELECT cod_fiscale
|
|
FROM disallineati2)
|
|
SELECT '' as 'C6MartPeriodico.PL_AreeMonitorate', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, NEED_AREA,
|
|
NEED_ESTESA,
|
|
NOME_PROGETTO,
|
|
--CONTROLLO NOME PROGETTO DUPLICATO, TOLTO IL MAX, AGGIUNTO NEL GROUP BY
|
|
ORDINAMENTO_PROGETTO,
|
|
--MAX(ORDINAMENTO_PROGETTO) AS ORDINAMENTO_PROGETTO,
|
|
MAX(ORDINE) AS ORDINE,
|
|
--rimettere il min e commentare il ctv=0
|
|
max(AREAPROGETTOSOTTOMONITORAGGIO) AS AREAPROGETTOSOTTOMONITORAGGIO
|
|
FROM ( --1.1 liq sotto monit
|
|
SELECT PIRMOD.ID_AREA AS Need_Area,
|
|
AREA.NOME_AREA AS Need_Estesa,
|
|
CASE WHEN PIRMOD.NOME_PROGETTO IS NULL
|
|
AND pirmod.id_area='Inv' THEN 'Prodotti non associati a progetti' ELSE LTRIM(RTRIM(PIRMOD.NOME_PROGETTO)) END AS Nome_Progetto,
|
|
PIRMOD.ORDINAMENTO_PROGETTO AS ORDINAMENTO_PROGETTO,
|
|
CASE PIRMOD.ID_AREA WHEN 'Liq' THEN 1 WHEN 'Ris' THEN 2 WHEN 'Pre' THEN 3 WHEN 'Inv' THEN 4 WHEN 'Ext' THEN 5 WHEN 'Na' THEN 6 END AS ORDINE,
|
|
CASE WHEN MONIT.COD_FISCALE IS NULL THEN 0 ELSE 1 END AS AREAPROGETTOSOTTOMONITORAGGIO
|
|
FROM
|
|
(SELECT RETE,
|
|
COD_FISCALE,
|
|
CASE WHEN ID_AREA IN ('Pre',
|
|
'Pre1',
|
|
'Pre2') THEN 'Pre' ELSE id_area END AS Id_area,
|
|
NOME_PROGETTO,
|
|
TIPO_PROGETTO,
|
|
PERC_AREA,
|
|
CTV_PROGETTO,
|
|
CTV_AREA,
|
|
ORIZZ_TEMP_NUM,
|
|
ORIZZ_TEMP,
|
|
CTV_TOTALE,
|
|
STATO_CONSULENZA,
|
|
DATA_STATO_CONS,
|
|
DATA_MIGR_CONS,
|
|
DATA_PIANIF,
|
|
ID_CONTRATTO,
|
|
---LEVARE???
|
|
ORDINAMENTO_PROGETTO
|
|
FROM C6MartPeriodico.CONS_ATTIVAGGIORNATA
|
|
WHERE COD_FISCALE=@CodiceFiscale
|
|
AND rete= @Rete ) PIRMOD
|
|
INNER JOIN C6MartPeriodico.AREA_BISOGNO AS AREA ON PIRMOD.ID_AREA = AREA.ID_AREA
|
|
LEFT OUTER JOIN C6MARTPERIODICO.MONITORAGGIO_ATTUALE MONIT ON MONIT.COD_FISCALE=PIRMOD.COD_FISCALE
|
|
AND MONIT.RETE=PIRMOD.RETE
|
|
AND MONIT.ID_AREA= PIRMOD.ID_AREA
|
|
AND MONIT.NOME_PROGETTO= ISNULL(PIRMOD.NOME_PROGETTO,'XXX')
|
|
AND MONIT.ORDINAMENTO_PROGETTO=PIRMOD.ORDINAMENTO_PROGETTO
|
|
WHERE PIRMOD.RETE= @Rete
|
|
AND PIRMOD.COD_FISCALE = @CodiceFiscale
|
|
AND PIRMOD.CTV_PROGETTO > 0
|
|
AND PIRMOD.ID_AREA NOT IN ('Pre1',
|
|
'Pre2',
|
|
'Cc') --V ordine cambiato da 5 a 6 inquanto 5 è lo stesso di ext, na è passato a 7
|
|
--Bido Added for 'SN' Saldo Negativo
|
|
UNION
|
|
SELECT DISTINCT 'Cc' AS Need_Area,
|
|
'Conti correnti a saldo negativo'AS Need_Estesa,
|
|
NULL AS Nome_Progetto,
|
|
--999999 as ORDINAMENTO_PROGETTO,
|
|
ORDINAMENTO_PROGETTO AS ORDINAMENTO_PROGETTO,
|
|
6 AS ORDINE,
|
|
0 AS AREAPROGETTOSOTTOMONITORAGGIO
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF PATRBF
|
|
WHERE PATRBF.RETE= @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.CTV < 0
|
|
AND id_area = 'CC' -- End Of Bido Added for 'SN' Saldo Negativo
|
|
UNION
|
|
SELECT DISTINCT 'Self' AS Need_Area,
|
|
'Liquidità negativa "Linee GP Eligo"' AS Need_Estesa,
|
|
NULL AS Nome_Progetto,
|
|
--999999 as ORDINAMENTO_PROGETTO,
|
|
ORDINAMENTO_PROGETTO AS ORDINAMENTO_PROGETTO,
|
|
7 AS ORDINE,
|
|
0 AS AREAPROGETTOSOTTOMONITORAGGIO
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF PATRBF
|
|
WHERE PATRBF.RETE= @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.CTV < 0
|
|
AND id_area = 'SELF' -- End Of Bido Added for 'SN' Saldo Negativo
|
|
UNION
|
|
SELECT DISTINCT 'Na' AS Need_Area,
|
|
'Non allocato'AS Need_Estesa,
|
|
NULL AS Nome_Progetto,
|
|
--999999 as ORDINAMENTO_PROGETTO,
|
|
ORDINAMENTO_PROGETTO AS ORDINAMENTO_PROGETTO,
|
|
8 AS ORDINE,
|
|
0 AS AREAPROGETTOSOTTOMONITORAGGIO
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF PATRBF
|
|
WHERE PATRBF.RETE= @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.CTV > 0
|
|
AND PATRBF.ID_AREA IN ('Na')
|
|
UNION
|
|
SELECT DISTINCT CASE WHEN PATRBF.ID_AREA IN ('Pre',
|
|
'Pre1',
|
|
'Pre2') THEN 'Pre' ELSE PATRBF.ID_AREA END AS Need_Area,
|
|
CASE WHEN PATRBF.ID_AREA IN ('Pre',
|
|
'Pre1',
|
|
'Pre2') THEN 'Previdenza' ELSE AREA.NOME_AREA END AS NEED_ESTESA,
|
|
CASE WHEN PATRBF.NOME_PROGETTO IS NULL
|
|
AND PATRBF.id_area='Inv' THEN 'Prodotti non associati a progetti' ELSE PATRBF.NOME_PROGETTO END AS Nome_Progetto,
|
|
--case when PATRBF.ID_AREA in ('Pre','Pre1','Pre2') then 1 else PATRBF.ORDINAMENTO_PROGETTO end as ORDINAMENTO_PROGETTO,
|
|
PATRBF.ORDINAMENTO_PROGETTO AS ORDINAMENTO_PROGETTO,
|
|
CASE PATRBF.ID_AREA WHEN 'Liq' THEN 1 WHEN 'Ris' THEN 2 WHEN 'Pre' THEN 3 WHEN 'Pre1' THEN 3 WHEN 'Pre2' THEN 3 WHEN 'Inv' THEN 4 WHEN 'Ext' THEN 5 WHEN 'Na' THEN 7 END AS ORDINE,
|
|
CASE WHEN MONIT.COD_FISCALE IS NULL THEN 0 ELSE 1 END AS AREAPROGETTOSOTTOMONITORAGGIO
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF PATRBF
|
|
INNER JOIN C6MartPeriodico.AREA_BISOGNO AS AREA ON PATRBF.ID_AREA = AREA.ID_AREA
|
|
LEFT OUTER JOIN C6MARTPERIODICO.MONITORAGGIO_ATTUALE MONIT ON MONIT.COD_FISCALE=PATRBF.COD_FISCALE
|
|
AND MONIT.RETE=PATRBF.RETE
|
|
AND MONIT.ID_AREA= PATRBF.ID_AREA
|
|
AND MONIT.NOME_PROGETTO= ISNULL(PATRBF.NOME_PROGETTO,'XXX')
|
|
AND MONIT.ORDINAMENTO_PROGETTO=PATRBF.ORDINAMENTO_PROGETTO
|
|
WHERE 1=1
|
|
AND PATRBF.RETE= @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.CTV > 0
|
|
AND PATRBF.EXTRANAC=1
|
|
AND PATRBF.ID_AREA NOT IN ('Na',
|
|
'Cc')) A
|
|
GROUP BY NEED_AREA,
|
|
NEED_ESTESA,
|
|
NOME_PROGETTO,
|
|
ORDINAMENTO_PROGETTO
|
|
ORDER BY ORDINE,
|
|
ORDINAMENTO_PROGETTO END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S137Bonus
|
|
BEGIN
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S137Bonus', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, [RETE],
|
|
bonus.[COD_FISCALE],
|
|
[DES_PROD] = CASE
|
|
WHEN desrc.descrizione is null THEN ''
|
|
ELSE desrc.descrizione
|
|
END,
|
|
bonus.[COD_CONF],
|
|
bonus.[CTV],
|
|
bonus.[DT_TRIM],
|
|
'Bonus Maturato' as [DESBONUS_M],
|
|
bonus.[BONUS_M],
|
|
bonus.[DTDEC_M],
|
|
'Bonus a scadenza 5 anni' as [DESBONUS_5],
|
|
bonus.[BONUS_5],
|
|
bonus.[DTDEC_5],
|
|
'Bonus a scadenza 10 anni' as [DESBONUS_10],
|
|
bonus.[BONUS_10],
|
|
bonus.[DTDEC_10],
|
|
bonus.[COD_PROD]
|
|
FROM [C6MartPeriodico].[BONUS] bonus
|
|
Left Join c6martperiodico.descrizione_bonus desrc
|
|
on desrc.cod_prod = bonus.cod_prod
|
|
WHERE
|
|
RETE = @Rete AND
|
|
COD_FISCALE = @CodiceFiscale
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S135MonitoraggioInvestimento
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S135MonitoraggioInvestimento', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
ID_AREA AS NEED_AREA,
|
|
CASE
|
|
WHEN NOME_PROGETTO = 'XXX' THEN ''
|
|
ELSE LTRIM(RTRIM(NOME_PROGETTO))
|
|
END AS NOME_PROGETTO,
|
|
ordinamento_progetto,
|
|
DATA_ATTIVAZIONE AS DATAVALORIZZAZIONE,
|
|
APPORTO_INIZIALE AS CONTROVALORE,
|
|
APPORTO_INIZIALE AS RISORSEASSOCIATE
|
|
FROM C6MartPeriodico.MONITORAGGIO_ATTUALE
|
|
WHERE RETE = @Rete
|
|
AND COD_FISCALE = @CodiceFiscale
|
|
UNION
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S135MonitoraggioInvestimento', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
ID_AREA,
|
|
CASE
|
|
WHEN NOME_PROGETTO = 'XXX' THEN ''
|
|
ELSE LTRIM(RTRIM(NOME_PROGETTO))
|
|
END AS NOME_PROGETTO,
|
|
ordinamento_progetto,
|
|
DATA_CTV,
|
|
CTV_MONITORATO,
|
|
ISNULL (RISORSE_ASSOCIATE, 0) -- ISNULL(CEDOLE_DIV, 0)
|
|
FROM C6MartPeriodico.MONITORAGGIO_STORICO
|
|
WHERE RETE = @Rete
|
|
AND COD_FISCALE = @CodiceFiscale
|
|
ORDER BY NEED_AREA, NOME_PROGETTO, DATAVALORIZZAZIONE
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S134MonitoraggioLiquidita
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
-- DECLARE @RendimNonRappr BIT
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S134MonitoraggioLiquidita', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
case when RENDIMNONRAPPR=1 then 1 else 0 end AS cf_rendim_non_rappr,
|
|
MONIT.id_Area AS Need_Area,
|
|
CASE
|
|
WHEN MONIT.NOME_PROGETTO = 'XXX' THEN ''
|
|
ELSE LTRIM(RTRIM(MONIT.NOME_PROGETTO))
|
|
END AS Nome_Progetto,
|
|
monit.ordinamento_progetto,
|
|
dbo.TOSHORTDATESTRING(DATA_ATTIVAZIONE) AS DataAttivazioneMonitoraggio,
|
|
CTV_MONITORATO AS ControvaloreMonitorato,
|
|
APPORTO_INIZIALE AS ApportoIniziale,
|
|
APPORTO_SUCCESSIVO AS ApportoSuccessivo,
|
|
LIQUIDAZIONI,
|
|
CEDOLE_DIVIDENDI AS CedoleEDividendi,
|
|
MINUS_PLUSVALENZA AS MinusPlusAreaProgetto,
|
|
RENDIMENTO_CUMULATO AS Rendimento_Attuale,
|
|
RISORSE_ASSOCIATE AS RisorseApportate,
|
|
dbo.TOSHORTDATESTRING(DATA_CTV) AS DataControvalore,
|
|
RENDIMENTO_ANNUALIZZATO
|
|
FROM
|
|
C6MartPeriodico.MONITORAGGIO_ATTUALE MONIT
|
|
LEFT OUTER JOIN
|
|
(
|
|
SELECT
|
|
PATR_BF.ID_AREA,
|
|
PATR_BF.COD_FISCALE,
|
|
PATR_BF.RETE,
|
|
CASE
|
|
WHEN PATR_BF.NOME_PROGETTO = 'XXX' THEN ''
|
|
ELSE NOME_PROGETTO end as NOME_PROGETTO,
|
|
patr_bf.ordinamento_progetto,
|
|
1 as RENDIMNONRAPPR
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF AS PATR_BF
|
|
LEFT OUTER JOIN C6MartPeriodico.AREA_BISOGNO AS AREA ON
|
|
PATR_BF.ID_AREA = AREA.ID_AREA
|
|
WHERE
|
|
PATR_BF.RETE = @Rete
|
|
AND PATR_BF.COD_FISCALE = @CodiceFiscale
|
|
AND PATR_BF.REND_NON_RAPPR = 1
|
|
group by
|
|
PATR_BF.ID_AREA,
|
|
PATR_BF.COD_FISCALE,
|
|
PATR_BF.RETE,
|
|
CASE
|
|
WHEN PATR_BF.NOME_PROGETTO = 'XXX' THEN ''
|
|
ELSE NOME_PROGETTO end,
|
|
patr_bf.ordinamento_progetto
|
|
) PATR_BF
|
|
ON PATR_BF.COD_FISCALE=MONIT.COD_FISCALE
|
|
AND PATR_BF.RETE= MONIT.RETE
|
|
AND left(PATR_BF.ID_AREA,3)=left(MONIT.ID_AREA,3)
|
|
AND isnull(PATR_BF.NOME_PROGETTO,'XXX')=isnull(MONIT.NOME_PROGETTO,'XXX')
|
|
And monit.ordinamento_progetto=patr_bf.ordinamento_progetto
|
|
WHERE
|
|
MONIT.RETE = @Rete AND
|
|
MONIT.COD_FISCALE = @CodiceFiscale
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S134BISMonitoraggioLiqInv
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S134BISMonitoraggioLiqInv', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
ID_AREA AS NEED_AREA,
|
|
CASE
|
|
WHEN NOME_PROGETTO = 'XXX' THEN ''
|
|
ELSE LTRIM(RTRIM(NOME_PROGETTO))
|
|
END AS NOME_PROGETTO,
|
|
ordinamento_progetto,
|
|
DATA_ATTIVAZIONE AS DATAVALORIZZAZIONE,
|
|
APPORTO_INIZIALE AS CONTROVALORE,
|
|
APPORTO_INIZIALE AS RISORSEASSOCIATE
|
|
FROM C6MartPeriodico.MONITORAGGIO_ATTUALE
|
|
WHERE RETE = @Rete
|
|
AND COD_FISCALE = @CodiceFiscale
|
|
UNION
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S134BISMonitoraggioLiqInv', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
ID_AREA,
|
|
CASE
|
|
WHEN NOME_PROGETTO = 'XXX' THEN ''
|
|
ELSE LTRIM(RTRIM(NOME_PROGETTO))
|
|
END AS NOME_PROGETTO,
|
|
ordinamento_progetto,
|
|
DATA_CTV,
|
|
CTV_MONITORATO,
|
|
ISNULL (RISORSE_ASSOCIATE, 0) -- ISNULL(CEDOLE_DIV, 0)
|
|
FROM C6MartPeriodico.MONITORAGGIO_STORICO
|
|
WHERE RETE = @Rete
|
|
AND COD_FISCALE = @CodiceFiscale
|
|
ORDER BY NEED_AREA, NOME_PROGETTO, DATAVALORIZZAZIONE
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S133BISPrincipaliProdotti
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
SELECT TOP 10 '' as 'C6MartPeriodico.PL_MP_S133BISPrincipaliProdotti', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
ANAGPROD.DESCR_PRODOTTO AS descrProdotto
|
|
,PATRBF.CTV AS Controvalore
|
|
,CASE
|
|
WHEN patrbf.tipo_prodotto = 'CC' THEN 0.00
|
|
ELSE RISCHIO_PROD.VAR_PERC_PTF
|
|
END AS VaRprodotto
|
|
,((CASE
|
|
WHEN ((PATRBF.CTV * RISCHIO_PROD.VAR_PERC_PTF)/100) is null then 0.00
|
|
ELSE(PATRBF.CTV * RISCHIO_PROD.VAR_PERC_PTF)/100
|
|
END) / (CASE
|
|
WHEN sum((PATRBF.CTV * isnull(RISCHIO_PROD.VAR_PERC_PTF,0))/100) over (partition by 'totale') = 0 THEN 1
|
|
ELSE sum((PATRBF.CTV * isnull(RISCHIO_PROD.VAR_PERC_PTF,0))/100) over (partition by 'totale')
|
|
END))*100 as rischioRelativo
|
|
,(PATRBF.CTV/(sum(PATRBF.CTV) over (partition by 'totale')))*100 AS pesoRelativo
|
|
,PATRBF.ID_AREA AS codAreaBisogno
|
|
FROM C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN C6MartPeriodico.ANAG_PRODOTTI ANAGPROD
|
|
ON ANAGPROD.COD_PRODOTTO = PATRBF.COD_PRODOTTO
|
|
INNER JOIN C6MartPeriodico.AREA_BISOGNO AREA
|
|
ON LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA
|
|
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_PROD
|
|
ON PATRBF.RETE= RISCHIO_PROD.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
|
|
AND RISCHIO_PROD.COD_AGGREG = CASE
|
|
WHEN (PATRBF.NOME_PROGETTO IS NOT NULL)
|
|
THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+
|
|
'|' + isnull(cast(patrbf.ordinamento_progetto as varchar),'') + '|'
|
|
WHEN LEFT(PATRBF.ID_AREA,3)='Na' then 'RISFIN|NA|'
|
|
ELSE 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
END
|
|
+ PATRBF.POSITION_ID
|
|
WHERE PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.CTV <>0
|
|
AND PATRBF.ID_AREA <> 'Cc'
|
|
ORDER BY rischioRelativo DESC
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S133AreeBisognoPrincProd
|
|
BEGIN
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S133AreeBisognoPrincProd', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, [NeedArea]
|
|
,[AreaName]
|
|
,[PercentualeModello]
|
|
,[PercentualeAttuale]
|
|
,[ControvaloreAttuale]
|
|
,[ControvaloreModello]
|
|
,[Totale_Percentuale]
|
|
,[DifferenzaEuro]
|
|
,[DifferenzaPercentuale]
|
|
,[var_needarea]
|
|
,[var_needareaString]
|
|
,[copertura_needarea]
|
|
,[copertura_needareaString]
|
|
,[Nota]
|
|
,[somma_controvalAttuale]
|
|
,[somma_controvalModello]
|
|
,[somma_percentualeAttuale]
|
|
,[somma_percentualeModello]
|
|
,[var_tot]
|
|
,[var_totString]
|
|
,[var_tot_pir]
|
|
,[var_tot_pirString]
|
|
,[copertura_tot]
|
|
,[copertura_totString]
|
|
,[copertura_tot_pir]
|
|
,[copertura_tot_pirString]
|
|
,[VAR_RISFIN]
|
|
,[VAR_RISFINSTRING]
|
|
,[copertura_risfin]
|
|
,[copertura_risfinString]
|
|
,[ordinamento]
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[TB_S133_S157DatiPiramide]
|
|
WHERE [Rete] = @Rete
|
|
AND [CodiceFiscale] = @CodiceFiscale
|
|
order by ordinamento
|
|
end
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S132ProfiloRischioAdeguatezza
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S132ProfiloRischioAdeguatezza', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, [RETE]
|
|
,[COD_FISCALE]
|
|
,[INSTITUTENAME]
|
|
,[CTV]
|
|
,[CODICEPROFILO]
|
|
,[DATAINIZIOVALIDITA]
|
|
,[VARMAX]
|
|
,[NOMEPROFILO]
|
|
,[VAR]
|
|
,[VARSTRING]
|
|
,[RISKCLASS]
|
|
,[RISKCLASSSTRING]
|
|
,[COVERAGE]
|
|
,[COVERAGESTRING]
|
|
,[RISKCLASSMAX]
|
|
,[PERCENTAGE]
|
|
,[VarIsOk]
|
|
,[RiskClassIsOk]
|
|
,[ComplexityIsOk]
|
|
,[Ully_perc]
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[TB_S132ProfiloRischioAdeguatezza]
|
|
WHERE [RETE] = @Rete
|
|
AND [COD_FISCALE] = @CodiceFiscale
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S146RischioDiversificazione
|
|
BEGIN
|
|
declare @contaCC2 as int
|
|
select @contaCC2 = count(*)
|
|
from c6martperiodico.patrimonio_Bf
|
|
where tipo_prodotto = 'CC' and cod_fiscale = @codiceFiscale and rete = @rete
|
|
declare @contaPOS2 as int
|
|
select @contaPOS2 = count(*)
|
|
from c6martperiodico.patrimonio_Bf
|
|
where cod_fiscale = @codiceFiscale and rete = @rete
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S146RischioDiversificazione', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
K.COD_FISCALE,
|
|
K.RETE,
|
|
CASE
|
|
WHEN (@contaPOS2 - @contaCC2 = 0) THEN 0
|
|
ELSE VAR_PERC_PTF
|
|
END AS VAR,
|
|
CASE WHEN ISNULL(COPERTURA,0.00) = 0.00 AND @contaPOS2 - @contaCC2 <> 0 THEN 'n.c.' ELSE NULL END as varString,
|
|
CASE
|
|
WHEN (ISNULL(copertura,100) < 100) AND @contaPOS2 - @contaCC2 <> 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,
|
|
beneficio as diversificazione,
|
|
CASE
|
|
WHEN (@contaPOS2 - @contaCC2 = 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 'Banca 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,
|
|
b.max_var as var_profilo,
|
|
--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
|
|
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')
|
|
AND k.COD_FISCALE = @COdiceFISCALE
|
|
AND k.RETE= @RETE
|
|
ORDER BY 11
|
|
end
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S141AnalisiRisparmioTable
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
declare @patr_compl2 decimal(18,3)
|
|
select @patr_compl2=sum(ctv_prodotto) from
|
|
c6martperiodico.patrimonio_emittenti
|
|
where cod_fiscale =@codicefiscale
|
|
and rete=@rete
|
|
and ctv_prodotto > 0
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S141AnalisiRisparmioTable', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,--U.*
|
|
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
|
|
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,
|
|
--RATING AS RATING,
|
|
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
|
|
FROM
|
|
(SELECT --Y.*
|
|
V.COD_FISCALE,
|
|
V.RETE,
|
|
INTERMEDIARIO,
|
|
CREDITRISKCLASS,
|
|
EMITTENTE,
|
|
--RATING AS RATING,
|
|
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
|
|
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,
|
|
RATING,
|
|
CREDITRISKCLASS
|
|
) Y
|
|
GROUP BY
|
|
COD_FISCALE,
|
|
RETE,
|
|
INTERMEDIARIO,
|
|
EMITTENTE
|
|
--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_compl2) *100 CONCENTRATION_ISSUER ,
|
|
0 AS CONCENTRATION,
|
|
sum(case when copertura > 0 then 0 else 1 end) as nc
|
|
FROM
|
|
(
|
|
SELECT --L.*
|
|
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
|
|
FROM
|
|
(
|
|
SELECT --V.*
|
|
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
|
|
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,
|
|
RATING,
|
|
INTERMEDIARIO,
|
|
CREDITRISKCLASS
|
|
) 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
|
|
) L
|
|
GROUP BY
|
|
COD_FISCALE,
|
|
RETE,
|
|
INTERMEDIARIO,
|
|
EMITTENTE
|
|
) U
|
|
ORDER BY U.CONCENTRATION DESC
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S140AnalisiRisparmioCharts
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
--QUERY PER INTERMEDIARIO
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S140AnalisiRisparmioCharts', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, RETE,
|
|
COD_FISCALE,
|
|
INTERMEDIARIO,
|
|
CASE
|
|
WHEN INTERMEDIARIO IN ('Banca Fideuram',
|
|
'Sanpaolo Invest') THEN 1
|
|
ELSE 2
|
|
END AS ORDINE,
|
|
SUM(CTV) AS CTV_TOT,
|
|
SUM(CASE WHEN RISKCLASS = 'NON COPERTO' THEN CTV ELSE 0 END) AS CTV_notCovered,
|
|
SUM(CASE WHEN RISKCLASS = 'Classe A'
|
|
OR RISKCLASS = 'Classe B'
|
|
OR RISKCLASS = 'Classe C'
|
|
OR RISKCLASS = 'Classe D' THEN CTV ELSE 0 END) AS CTV_CreditRisk,
|
|
SUM(CASE WHEN RISKCLASS = 'NA' THEN CTV ELSE 0 END) AS CTV_na,
|
|
--CASE WHEN SUM(CTV)=0 THEN 0 ELSE (SUM(CASE WHEN RISKCLASS = 'NON COPERTO' THEN CTV ELSE 0 END)/SUM(CTV))*100 END AS PERC_notCovered,
|
|
CASE
|
|
WHEN SUM(CTV)=0 THEN 0
|
|
ELSE (SUM(CASE WHEN RISKCLASS = 'Classe A'
|
|
OR RISKCLASS = 'Classe B'
|
|
OR RISKCLASS = 'Classe C'
|
|
OR RISKCLASS = 'Classe D' THEN CTV ELSE 0 END) /SUM(CASE WHEN RISKCLASS <> 'NON COPERTO' THEN CTV ELSE 1 END))*100
|
|
END AS PERC_CreditRisk,
|
|
CASE
|
|
WHEN SUM(CTV)=0 THEN 0
|
|
ELSE (SUM(CASE WHEN RISKCLASS = 'NA' THEN CTV ELSE 0 END) /sum(CASE WHEN RISKCLASS <> 'NON COPERTO' THEN CTV ELSE 1 END))*100
|
|
END AS PERC_NA
|
|
FROM ( -- Start 1.1
|
|
SELECT PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIO_PROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END AS RISKCLASS,
|
|
CASE
|
|
WHEN PATRBF.RETE = 'F' THEN 'Banca Fideuram'
|
|
ELSE 'Sanpaolo Invest'
|
|
END AS INTERMEDIARIO,
|
|
--SUM(CASE WHEN PATRBF.ID_AREA = 'Cc' AND PATRBF.CTV < 0 THEN 0 ELSE PATRBF.CTV END) AS CTV
|
|
--INIZIO INTERVENTI OMNIA
|
|
--SUM(CASE WHEN ctvcc.cc_ctv <0 and patrbf.id_area='cc' THEN 0 ELSE PATRBF.CTV END) AS ctv
|
|
SUM(CASE WHEN ctvcc.cc_ctv <0
|
|
AND patrbf.id_area='cc' THEN 0 WHEN ctvself.self_ctv <0
|
|
AND patrbf.id_area='self' THEN 0 ELSE PATRBF.CTV END) AS ctv --FINE INTERVENTI OMNIA
|
|
FROM [C6Martperiodico].PATRIMONIO_BF PATRBF ---Per calcolare la somma algebrica dei cc
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS cc_ctv
|
|
FROM [C6MARTPERIODICO].pATRIMONIO_BF
|
|
WHERE id_Area='Cc'
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvcc ON patrbf.cod_fiscale=ctvcc.cod_fiscale
|
|
AND patrbf.rete=ctvcc.rete --fine calcolo somma cc
|
|
--INIZIO INTERVENTI OMNIA
|
|
---Per calcolare la somma algebrica dei self negativi
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS self_ctv
|
|
FROM [C6MARTPERIODICO].pATRIMONIO_BF
|
|
WHERE id_Area='Self'
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvself ON patrbf.cod_fiscale=ctvself.cod_fiscale
|
|
AND patrbf.rete=ctvself.rete --fine calcolo somma self negativi
|
|
--FINE INTERVENTI OMNIA
|
|
LEFT OUTER JOIN [C6Martperiodico].RISCHIO_AGGREGATO RISCHIO_PROD ON PATRBF.RETE= RISCHIO_PROD.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
|
|
AND RISCHIO_PROD.COD_AGGREG = CASE
|
|
WHEN (PATRBF.NOME_PROGETTO IS NOT NULL) THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO + '|' + isnull(cast(PATRBF.ordinamento_progetto AS varchar),'') + '|' --V
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('EXT',
|
|
'INV',
|
|
'PRE',
|
|
'RIS',
|
|
'LIQ') THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('CC') THEN 'COMPLESSIVO|BF|CC|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('NA') THEN 'RISFIN|' + LEFT(PATRBF.ID_AREA,3) + '|' --
|
|
END + PATRBF.POSITION_ID --INNER JOIN C6MartPeriodico.CODIFICA_CREDITRISK CR
|
|
LEFT OUTER JOIN C6MartPeriodico.CODIFICA_CREDITRISK CR ON RISCHIO_PROD.CREDITRISK = cr.profilo --V
|
|
WHERE PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.RETE = @Rete --
|
|
GROUP BY PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIO_PROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END,
|
|
CASE
|
|
WHEN PATRBF.RETE = 'F' THEN 'FIDEURAM'
|
|
ELSE 'SANPAOLO'
|
|
END --- End 1.1
|
|
UNION ALL -- Start 1.2
|
|
SELECT PATRTERZI.RETE,
|
|
PATRTERZI.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIOPROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END AS RISKCLASS,
|
|
PATRTERZI.INTERMEDIARIO,
|
|
SUM(ROUND(CTV,2)) AS CTV
|
|
FROM [C6Martperiodico].PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS cc_ctv
|
|
FROM [C6Martperiodico].pATRIMONIO_terzi
|
|
WHERE tipo_prodotto='Conti correnti'
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvcc ON patrterzi.cod_fiscale=ctvcc.cod_fiscale
|
|
AND patrterzi.rete=ctvcc.rete
|
|
LEFT OUTER JOIN [C6Martperiodico].RISCHIO_AGGREGATO RISCHIOPROD ON PATRTERZI.RETE = RISCHIOPROD.RETE
|
|
AND PATRTERZI.COD_FISCALE = RISCHIOPROD.COD_FISCALE
|
|
AND RISCHIOPROD.COD_AGGREG ='TIPOPRODTERZI|TERZI|' +ISNULL(PATRTERZI.DESCR_GRUPPO_PRODOTTO, PATRTERZI.TIPO_PRODOTTO) + '|'+ PATRTERZI.COD_PRODOTTO_TERZI
|
|
LEFT OUTER JOIN [C6Martperiodico].CODIFICA_CREDITRISK CR ON RISCHIOPROD.CREDITRISK = cr.profilo --V
|
|
WHERE PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
AND PATRTERZI.RETE = @Rete --
|
|
GROUP BY PATRTERZI.RETE,
|
|
PATRTERZI.COD_FISCALE,
|
|
PATRTERZI.INTERMEDIARIO,
|
|
CASE
|
|
WHEN RISCHIOPROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END -- End 1.2
|
|
)H
|
|
GROUP BY RETE,
|
|
COD_FISCALE,
|
|
INTERMEDIARIO --------
|
|
UNION ALL -----------
|
|
--QUERY TOTALE
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S140AnalisiRisparmioCharts', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, RETE,
|
|
COD_FISCALE,
|
|
'Complessivo' AS INTERMEDIARIO,
|
|
3 AS ORDINE,
|
|
SUM(CTV) AS CTV_TOT,
|
|
SUM(CASE WHEN RISKCLASS = 'NON COPERTO' THEN CTV ELSE 0 END) AS CTV_notCovered,
|
|
SUM(CASE WHEN RISKCLASS = 'Classe A'
|
|
OR RISKCLASS = 'Classe B'
|
|
OR RISKCLASS = 'Classe C'
|
|
OR RISKCLASS = 'Classe D' THEN CTV ELSE 0 END) AS CTV_CreditRisk,
|
|
SUM(CASE WHEN RISKCLASS ='NA' THEN CTV ELSE 0 END) AS CTV_na,
|
|
--CASE WHEN SUM(CTV)=0 THEN 0 ELSE (SUM(CASE WHEN RISKCLASS = 'NON COPERTO' THEN CTV ELSE 0 END)/SUM(CTV))*100 END AS PERC_notCovered,
|
|
CASE
|
|
WHEN SUM(CTV)=0 THEN 0
|
|
ELSE (SUM(CASE WHEN RISKCLASS = 'Classe A'
|
|
OR RISKCLASS = 'Classe B'
|
|
OR RISKCLASS = 'Classe C'
|
|
OR RISKCLASS = 'Classe D' THEN CTV ELSE 0 END) /SUM(CASE WHEN RISKCLASS <> 'NON COPERTO' THEN CTV ELSE 1 END))*100
|
|
END AS PERC_CreditRisk,
|
|
CASE
|
|
WHEN SUM(CTV)=0 THEN 0
|
|
ELSE (SUM(CASE WHEN RISKCLASS = 'NA' THEN CTV ELSE 0 END) /sum(CASE WHEN RISKCLASS <> 'NON COPERTO' THEN CTV ELSE 1 END))*100
|
|
END AS PERC_NA
|
|
FROM (--- Start 2.1
|
|
SELECT PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIO_PROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END AS RISKCLASS,
|
|
--SUM(CASE WHEN PATRBF.ID_AREA = 'Cc' AND PATRBF.CTV < 0 THEN 0 ELSE PATRBF.CTV END) AS CTV
|
|
--INIZIO INTERVENTI OMNIA
|
|
--SUM(CASE WHEN ctvcc.cc_ctv <0 and patrbf.id_area='cc' THEN 0 ELSE PATRBF.CTV END) AS CTV
|
|
SUM(CASE WHEN ctvcc.cc_ctv <0
|
|
AND patrbf.id_area='cc' THEN 0 WHEN ctvself.self_ctv <0
|
|
AND patrbf.id_area='self' THEN 0 ELSE PATRBF.CTV END) AS CTV --FINE INTERVENTI OMNIA
|
|
FROM [C6Martperiodico].PATRIMONIO_BF PATRBF --per fare la somma algebrica dei cc
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS cc_ctv
|
|
FROM [C6MARTPERIODICO].pATRIMONIO_BF
|
|
WHERE id_Area='Cc'
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvcc ON patrbf.cod_fiscale=ctvcc.cod_fiscale
|
|
AND patrbf.rete=ctvcc.rete --fine somma cc
|
|
--INIZIO INTERVENTI OMNIA
|
|
--per fare la somma algebrica dei self negativi
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS self_ctv
|
|
FROM [C6MARTPERIODICO].pATRIMONIO_BF
|
|
WHERE id_Area='Self'
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvself ON patrbf.cod_fiscale=ctvself.cod_fiscale
|
|
AND patrbf.rete=ctvself.rete --fine somma Self negativi
|
|
--FINE INTERVENTI OMNIA
|
|
LEFT OUTER JOIN [C6Martperiodico].RISCHIO_AGGREGATO RISCHIO_PROD ON PATRBF.RETE= RISCHIO_PROD.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
|
|
AND RISCHIO_PROD.COD_AGGREG = CASE
|
|
WHEN (PATRBF.NOME_PROGETTO IS NOT NULL) THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+ '|' + isnull(cast(PATRBF.ordinamento_progetto AS varchar),'')+'|' --V
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('EXT',
|
|
'INV',
|
|
'PRE',
|
|
'RIS',
|
|
'LIQ') THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('CC') THEN 'COMPLESSIVO|BF|CC|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
WHEN (PATRBF.NOME_PROGETTO IS NULL)
|
|
AND PATRBF.ID_AREA IN ('NA') THEN 'RISFIN|' + LEFT(PATRBF.ID_AREA,3) + '|' --
|
|
END + PATRBF.POSITION_ID
|
|
LEFT OUTER JOIN [C6Martperiodico].CODIFICA_CREDITRISK CR ON RISCHIO_PROD.CREDITRISK = cr.profilo --V
|
|
WHERE PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.RETE = @Rete --
|
|
GROUP BY PATRBF.RETE,
|
|
PATRBF.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIO_PROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END -- End 2.1
|
|
UNION ALL -- Start 2.2
|
|
SELECT PATRTERZI.RETE,
|
|
PATRTERZI.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIOPROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END AS RISKCLASS,
|
|
SUM(ROUND(CTV,2)) AS CTV
|
|
FROM [C6Martperiodico].PATRIMONIO_TERZI PATRTERZI
|
|
LEFT OUTER JOIN
|
|
(SELECT cod_fiscale,
|
|
rete,
|
|
sum(ctv) AS cc_ctv
|
|
FROM [C6Martperiodico].pATRIMONIO_terzi
|
|
WHERE tipo_prodotto='Conti correnti'
|
|
GROUP BY cod_fiscale,
|
|
rete) ctvcc ON patrterzi.cod_fiscale=ctvcc.cod_fiscale
|
|
AND patrterzi.rete=ctvcc.rete
|
|
LEFT OUTER JOIN [C6Martperiodico].RISCHIO_AGGREGATO RISCHIOPROD ON PATRTERZI.RETE = RISCHIOPROD.RETE
|
|
AND PATRTERZI.COD_FISCALE = RISCHIOPROD.COD_FISCALE
|
|
AND RISCHIOPROD.COD_AGGREG ='TIPOPRODTERZI|TERZI|' + ISNULL(PATRTERZI.DESCR_GRUPPO_PRODOTTO, PATRTERZI.TIPO_PRODOTTO) + '|'+ PATRTERZI.COD_PRODOTTO_TERZI
|
|
LEFT OUTER JOIN [C6Martperiodico].CODIFICA_CREDITRISK CR ON RISCHIOPROD.CREDITRISK = cr.profilo --V
|
|
WHERE PATRTERZI.COD_FISCALE = @CodiceFiscale
|
|
AND PATRTERZI.RETE = @Rete --
|
|
GROUP BY PATRTERZI.RETE,
|
|
PATRTERZI.COD_FISCALE,
|
|
CASE
|
|
WHEN RISCHIOPROD.COPERTURA < 100 THEN 'NON COPERTO'
|
|
WHEN DESCRIZIONE IS NULL THEN 'NA'
|
|
ELSE DESCRIZIONE
|
|
END --- End 2.2
|
|
)H
|
|
GROUP BY RETE,
|
|
COD_FISCALE END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S137BISCapitaleProtetto
|
|
BEGIN
|
|
--SELECT top 1
|
|
-- 'Interfund Capital Protection' AS DES_PROD,
|
|
-- '001234.RS.S678' AS COD_CONF,
|
|
-- '120,1040' AS QUOTA,
|
|
-- '100,1234' AS NUMEROQUOTE,
|
|
-- '12.025,22' AS CONTROVALORE,
|
|
-- '31/03/2014' AS DATARIF,
|
|
-- 'Quota massima' AS DES_MAX,
|
|
-- '125,4573' AS QUOTA_MAX,
|
|
-- '80' AS QUOTA_MAX_PERC,
|
|
-- '31/03/2013' AS DATARIF_MAX,
|
|
-- 'Quota e controvalore protetti*' AS DES_PROT,
|
|
-- '100,3658' AS QUOTA_PROT,
|
|
-- '100,1234' AS NUMEROQUOTE_PROT,
|
|
-- '10.048,97' AS CONTROVALORE_PROT
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S137BISCapitaleProtetto', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
DES_PROD
|
|
,COD_CONF
|
|
,QUOTA
|
|
,NUMEROQUOTE
|
|
,CONTROVALORE
|
|
,DATARIF
|
|
,DES_MAX
|
|
,QUOTA_MAX
|
|
,QUOTA_MAX_PERC
|
|
,DATARIF_MAX
|
|
,DES_PROT
|
|
,QUOTA_PROT
|
|
,NUMEROQUOTE_PROT
|
|
,CONTROVALORE_PROT
|
|
FROM C6MartPeriodico.CAPPROT
|
|
WHERE RETE = @Rete
|
|
AND CODFIS = @CodiceFiscale
|
|
Union
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S137BISCapitaleProtetto', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
DES_PROD
|
|
,COD_CONF
|
|
,QUOTA
|
|
,NUMEROQUOTE
|
|
,CONTROVALORE
|
|
,DATARIF
|
|
,DES_MAX
|
|
,QUOTA_MAX
|
|
,QUOTA_MAX_PERC
|
|
,DATARIF_MAX
|
|
,DES_PROT
|
|
,QUOTA_PROT
|
|
,NUMEROQUOTE_PROT
|
|
,CONTROVALORE_PROT
|
|
FROM C6MartPeriodico.CAPPROT
|
|
WHERE RETE = @Rete
|
|
AND CODFIS = @CodiceFiscale
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S43ContoCorrenteBF
|
|
BEGIN
|
|
SELECT '' as 'C6MartPeriodico.PL_S43ContoCorrenteBF', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
dbo.ToShortDateString(ANAG_CC.DATA_APERTURA) AS datadiSottoscrizione,
|
|
ANAG_CC.COD_CC AS conto,
|
|
cast(ANAG_CC.SALDOCONTABILE as decimal(15,2)) as Controvalore,
|
|
cast(ANAG_CC.SALDODISPONIBILE as decimal(15,2)) as SALDO_DISPONIBILE,
|
|
null as creditrisk ,
|
|
'n.a.' as creditriskstring,
|
|
0 as var,
|
|
null as varString,
|
|
0 as totaleVar,
|
|
null as totaleVarString
|
|
FROM
|
|
C6MartPeriodico.ANAG_CC
|
|
WHERE
|
|
ANAG_CC.rete= @Rete
|
|
AND ANAG_CC.cod_fiscale= @CodiceFiscale
|
|
AND ANAG_CC.SALDOCONTABILE < 0
|
|
ORDER BY
|
|
ANAG_CC.SALDOCONTABILE DESC
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S43BISDettaglioProdottiBF
|
|
BEGIN
|
|
-------------------------------------------------------------------------------------
|
|
--INIZIO INTERVENTI OMNIA QUIII
|
|
DECLARE @TOTSELFLIQ3 AS INT
|
|
SELECT @TOTSELFLIQ3 = COUNT(*)
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF
|
|
WHERE PATRIMONIO_BF.RETE = @Rete
|
|
AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
|
|
AND ISIN in('EURO00000009', 'EURO10000007')
|
|
AND ID_AREA = 'LIQ'
|
|
DECLARE @TOTLIQ3 AS INT
|
|
SELECT @TOTLIQ3 = COUNT(*)
|
|
FROM C6MARTPERIODICO.PATRIMONIO_BF
|
|
WHERE PATRIMONIO_BF.RETE = @Rete
|
|
AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
|
|
AND ID_AREA = 'LIQ'
|
|
--FINE INTERVENTI OMNIA QUIII
|
|
--------------------------------------------------------------------------------------
|
|
SELECT '' as 'C6MartPeriodico.PL_S43BISDettaglioProdottiBF', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Pre'
|
|
ELSE AREA.ID_AREA
|
|
END AS needarea,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Previdenza'
|
|
WHEN AREA.ID_AREA = 'Na' THEN 'Non allocate'
|
|
ELSE AREA.NOME_AREA
|
|
END AS needareades,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 3
|
|
ELSE AREA.ORDINAMENTO
|
|
END AS areasortorder,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND PATRBF.NOME_PROGETTO IS NULL THEN 'Prodotti non associati a progetti'
|
|
ELSE ISNULL(PATRBF.NOME_PROGETTO,'')
|
|
END AS nome_progetto,
|
|
PATRBF.ORDINAMENTO_PROGETTO,
|
|
--Bido
|
|
SUM(PATRBF.CTV ) OVER (PARTITION BY AREA.ID_AREA) AS somma_controval_needarea,
|
|
SUM(PATRBF.CTV - MONIT.UTILE_PERDITA_DA_ASSOC) OVER (PARTITION BY AREA.ID_AREA) AS versato_needarea,
|
|
CASE
|
|
WHEN COUNT(CASE WHEN (PATRBF.CTV - MONIT.UTILE_PERDITA_DA_ASSOC) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END as versato_needareaString,
|
|
SUM(MONIT.UTILE_PERDITA_DA_ASSOC) OVER (PARTITION BY AREA.ID_AREA) AS MinusPlusValenza_needarea,
|
|
CASE
|
|
WHEN COUNT( CASE
|
|
WHEN MONIT.UTILE_PERDITA_DA_ASSOC IS NULL THEN 1
|
|
ELSE NULL
|
|
END ) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_needareaString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.ordinamento_progetto)
|
|
ELSE NULL
|
|
END AS somma_controval_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV - MONIT.UTILE_PERDITA_DA_ASSOC) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.ordinamento_progetto)
|
|
ELSE NULL
|
|
END AS versato_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN (PATRBF.CTV - MONIT.UTILE_PERDITA_DA_ASSOC) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.ordinamento_progetto) > 0
|
|
THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS versato_need_progString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN SUM(MONIT.UTILE_PERDITA_DA_ASSOC) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.ordinamento_progetto)
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN MONIT.UTILE_PERDITA_DA_ASSOC IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.ordinamento_progetto) > 0
|
|
THEN 'n.d.'
|
|
ELSE NULL
|
|
END AS MinusPlusValenza_need_progString,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.VAR_PERC_PTF
|
|
ELSE NULL
|
|
END AS somma_var_need_prog,
|
|
CASE
|
|
WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.COPERTURA
|
|
ELSE NULL
|
|
END AS somma_copertura_need_prog,
|
|
RISCHIO_AGGR_AREA.VAR_PERC_PTF AS var_needarea,
|
|
CASE
|
|
WHEN ISNULL(SUM(RISCHIO_AGGR_AREA.COPERTURA) OVER (PARTITION BY AREA.ID_AREA),0.00) = 0.00
|
|
THEN 'n.c.'
|
|
--------------------------------------------------------------
|
|
--INIZIO INTERVENTI OMNIA QUIII
|
|
WHEN @TOTSELFLIQ3 = @TOTLIQ3 and AREA.ID_AREA = 'LIQ'
|
|
THEN 'n.c.'
|
|
--FINE INTERVENTI OMNIA QUIII
|
|
--------------------------------------------------------------
|
|
ELSE NULL
|
|
END AS var_needareaString,
|
|
CAST(ISNULL(RISCHIO_AGGR_AREA.COPERTURA,100) AS DECIMAL (5,2)) AS copertura_needarea,
|
|
CASE
|
|
WHEN (ISNULL(RISCHIO_AGGR_AREA.copertura,100) < 100)
|
|
--V THEN '(*) Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
ELSE ''
|
|
END AS copertura_needareaString,
|
|
RISCHIO_AGGR_PROG.VAR_PERC_PTF AS var_need_prog,
|
|
CASE
|
|
WHEN ISNULL(RISCHIO_AGGR_PROG.COPERTURA,0.00) = 0.00 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS var_need_progString,
|
|
RISCHIO_AGGR_PROG.COPERTURA AS copertura_need_prog,
|
|
--TRINGALI
|
|
CASE
|
|
WHEN (ISNULL(RISCHIO_AGGR_PROG.copertura,100) < 100)
|
|
--v THEN '(*) Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_PROG.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_PROG.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
|
|
ELSE ''
|
|
END AS copertura_need_progString,
|
|
--INIZIO INTERVENTI OMNIA QUIIIIIIII
|
|
--case when (dbo.TOSHORTDATESTRING(MONIT.DATA_ASSOCIAZIONE) is null
|
|
-- and patrbf.tipo_prodotto='CC'
|
|
-- and patrbf.id_area='liq')
|
|
--then
|
|
-- CASE WHEN patrbf.data_sottoscrizione>monatt.data_attivazione
|
|
-- THEN
|
|
-- dbo.TOSHORTDATESTRING(patrbf.data_sottoscrizione)
|
|
-- ELSE
|
|
-- CASE WHEN monatt.data_attivazione> '20110211'
|
|
-- then dbo.TOSHORTDATESTRING(monatt.data_attivazione)
|
|
-- ELSE dbo.TOSHORTDATESTRING('20110211')
|
|
-- END
|
|
-- END
|
|
-- END
|
|
case when (dbo.TOSHORTDATESTRING(MONIT.DATA_ASSOCIAZIONE) is null
|
|
and patrbf.id_area='liq'
|
|
and (patrbf.tipo_prodotto='CC'
|
|
or patrbf.isin in('EURO00000009', 'EURO10000007')))
|
|
then dbo.TOSHORTDATESTRING(C6MartPeriodico.getTrimestre2(getdate(),0))
|
|
--FINE INTERVENTI OMNIA
|
|
else
|
|
--disasteriscare
|
|
--dbo.TOSHORTDATESTRING(MONIT.DATA_ASSOCIAZIONE)
|
|
--cancellare inizio
|
|
case when (dbo.TOSHORTDATESTRING(MONIT.DATA_ASSOCIAZIONE) is null
|
|
and patrbf.chiave_progetto = '2205396'
|
|
and patrbf.tipo_prodotto = 'FI')
|
|
then '26/06/2014'
|
|
else
|
|
dbo.TOSHORTDATESTRING(MONIT.DATA_ASSOCIAZIONE)
|
|
end
|
|
--cancellare fine
|
|
end AS dataAssociazione, -- CAMBIATA TRINGALI--marianna, cambiata per i cc senza data di associazione
|
|
PATRBF.ID_CONTRATTO AS ContrattoDossier,
|
|
--V
|
|
CASE WHEN PATRBF.TIPO_PRODOTTO <> 'CC' THEN ANAGPROD.DESCR_PRODOTTO
|
|
ELSE ANAGPROD.DESCR_PRODOTTO+' '+SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC))
|
|
END AS Descrizione,
|
|
--ANAGPROD.DESCR_PRODOTTO AS Descrizione,
|
|
PATRBF.CTV AS Controvalore,
|
|
CASE WHEN PATRBF.REND_NON_RAPPR=1 THEN NULL ELSE isnull(MONIT.VERSATO_NETTO_DA_ASSOC,0) end AS VersatoNetto,
|
|
CASE WHEN (MONIT.VERSATO_NETTO_DA_ASSOC IS NULL OR PATRBF.REND_NON_RAPPR=1) THEN 'n.d.' ELSE NULL END as VersatoNettoString,
|
|
CASE WHEN PATRBF.REND_NON_RAPPR=1 THEN NULL ELSE isnull(MONIT.UTILE_PERDITA_DA_ASSOC,0) END AS MinusPlusValenza,
|
|
CASE WHEN (MONIT.UTILE_PERDITA_DA_ASSOC IS NULL OR PATRBF.REND_NON_RAPPR=1) THEN 'n.d.' ELSE NULL END as MinusPlusValenzaString,
|
|
case when (patrbf.tipo_prodotto ='CC') then 0.00
|
|
else RISCHIO_PROD.VAR_PERC_PTF
|
|
end AS VaRprodotto,
|
|
--INIZIO INTERVENTI OMNIA
|
|
--CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 and patrbf.tipo_prodotto <> 'CC' THEN 'n.c.' ELSE NULL END as varProdottoString,
|
|
CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 and patrbf.tipo_prodotto <>'CC' THEN 'n.c.'
|
|
WHEN patrbf.isin in ('EURO00000009', 'EURO10000007' )THEN 'n.c.'
|
|
ELSE NULL END as varProdottoString,
|
|
--FINE INTERVENTI OMNIA
|
|
--INIZIO INTERVENTI OMNIA
|
|
--case when (patrbf.tipo_prodotto ='CC') then 100.00
|
|
case when (patrbf.tipo_prodotto ='CC') or (patrbf.isin in('EURO00000009', 'EURO10000007')) then 100.00
|
|
--FINE INTERVENTI OMNIA
|
|
else RISCHIO_PROD.Copertura
|
|
end AS Coperturaprodotto,
|
|
PATRBF.REND_ANNO AS PerformanceYTD,
|
|
PATRBF.REND_SOTTOSCRIZIONE AS performanceDS,
|
|
PARTVIA_DISINV AS partitaViaggiante,
|
|
CASE WHEN (ISNULL(RISCHIO_AGGR_AREA.copertura,100) < 100) THEN '*' ELSE '' END AS nota_needarea,
|
|
RISCHIO_PROD.CREDITRISK as creditrisk,
|
|
CASE
|
|
--INIZIO INTERVENTI OMNIA
|
|
-- WHEN ISNULL(RISCHIO_PROD.COPERTURA,0.00) = 0.00 and patrbf.tipo_prodotto <> 'CC' THEN 'n.c.'
|
|
WHEN (ISNULL(RISCHIO_prod.COPERTURA,0.00) = 0.00
|
|
and patrbf.tipo_prodotto <> 'CC')
|
|
or (patrbf.isin in('EURO00000009', 'EURO10000007'))
|
|
THEN 'n.c.'
|
|
--FINE INTERVENTI OMNIA
|
|
WHEN RISCHIO_PROD.CREDITRISK is null THEN 'n.a.'
|
|
ELSE NULL
|
|
END as creditriskstring,
|
|
-- CASE
|
|
-- WHEN RISCHIO_PROD.CREDITRISKEMIT IS NULL THEN 'n.a.'
|
|
-- ELSE cast(RISCHIO_PROD.CREDITRISKEMIT as varchar(20))
|
|
-- END as creditriskemit
|
|
RISCHIO_PROD.CREDITRISKEMIT as creditriskemit
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN
|
|
C6MartPeriodico.ANAG_PRODOTTI ANAGPROD
|
|
ON
|
|
ANAGPROD.COD_PRODOTTO = PATRBF.COD_PRODOTTO
|
|
INNER JOIN
|
|
C6MartPeriodico.AREA_BISOGNO AREA
|
|
ON
|
|
-- LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA
|
|
(LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA and AREA.ID_AREA <>'self') or (PATRBF.ID_AREA='self' and AREA.ID_AREA ='self')
|
|
LEFT JOIN
|
|
C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_PROD
|
|
ON
|
|
PATRBF.RETE= RISCHIO_PROD.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
|
|
AND RISCHIO_PROD.COD_AGGREG =
|
|
CASE
|
|
WHEN (PATRBF.NOME_PROGETTO IS NOT NULL)
|
|
THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO + '|'
|
|
+ cast (PatrBF.ordinamento_progetto as varchar) + '|'
|
|
when LEFT(PATRBF.ID_AREA,3)='Na' then 'RISFIN|NA|'
|
|
ELSE 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|'
|
|
END
|
|
-- END + PATRBF.POSITION_ID
|
|
+ PATRBF.POSITION_ID
|
|
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_PROG
|
|
ON PATRBF.RETE= RISCHIO_AGGR_PROG.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_AGGR_PROG.COD_FISCALE
|
|
AND RISCHIO_AGGR_PROG.COD_AGGREG = 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO + '|'
|
|
+ cast (PatrBF.ordinamento_progetto as varchar)
|
|
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_AREA
|
|
ON PATRBF.RETE= RISCHIO_AGGR_AREA.RETE
|
|
AND PATRBF.COD_FISCALE= RISCHIO_AGGR_AREA.COD_FISCALE
|
|
AND RISCHIO_AGGR_AREA.COD_AGGREG = case
|
|
when LEFT(PATRBF.ID_AREA,3) = 'Na' then 'RISFIN|NA'
|
|
else 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3)
|
|
end
|
|
-- AND RISCHIO_AGGR_AREA.COD_AGGREG =
|
|
-- CASE AREA.ID_AREA
|
|
-- WHEN 'Na' THEN 'RISORSENONASSOCIATE'
|
|
-- ELSE 'COMPLESSIVO|BF|' + LEFT(PATRBF.ID_AREA,3)
|
|
-- END
|
|
LEFT JOIN
|
|
--MODIFICA TRINGALI PER PRENDERE DETTAGLIO MONITORAGGIO
|
|
C6MartPeriodico.MONITORAGGIO_DETTAGLIO MONIT
|
|
ON
|
|
PATRBF.RETE = MONIT.RETE
|
|
AND PATRBF.COD_FISCALE = MONIT.COD_FISCALE
|
|
--AND LEFT(PATRBF.ID_AREA, 3) = MONIT.ID_AREA
|
|
and PATRBF.POSITION_ID=MONIT.POSITION_ID
|
|
--AND CASE WHEN PATRBF.NOME_PROGETTO IS NULL THEN 'XXX' ELSE PATRBF.NOME_PROGETTO END = MONIT.NOME_PROGETTO
|
|
--Bido to get Code_CC field
|
|
LEFT OUTER JOIN C6MartPeriodico.ANAG_CC
|
|
ON
|
|
ANAG_CC.COD_FISCALE = PATRBF.COD_FISCALE
|
|
AND ANAG_CC.RETE = PATRBF.RETE
|
|
AND cast(ANAG_CC.cod_cc as integer ) = substring(PATRBF.position_id,72,9)
|
|
LEFT OUTER JOIN C6MARTPERIODICO.MONITORAGGIO_ATTUALE MONATT
|
|
ON
|
|
PATRBF.RETE = MONATT.RETE
|
|
AND PATRBF.COD_FISCALE = MONATT.COD_FISCALE
|
|
AND
|
|
(PATRBF.ID_AREA=(CASE WHEN MONATT.ID_AREA='Exr' THEN 'Ext' ELSE MONATT.ID_AREA END) AND PATRBF.ID_AREA <> 'INV'
|
|
OR
|
|
PATRBF.ID_AREA = 'INV' AND PATRBF.ID_AREA = MONATT.ID_AREA AND PATRBF.ORDINAMENTO_PROGETTO = MONATT.ORDINAMENTO_PROGETTO
|
|
)
|
|
--blocchiamo il monitoraggio per i progetti a ctv=0
|
|
--And ctv_monitorato>0
|
|
WHERE 1 = 1
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.CTV <> 0
|
|
ORDER BY
|
|
AREASORTORDER, PATRBF.NOME_PROGETTO, PATRBF.CTV DESC -- modifica TRINGALI
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S88PatrimonioNonConsideratoNelCalcoloDelRendimento
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
SELECT '' as 'C6MartPeriodico.PL_S88PatrimonioNonConsideratoNelCalcoloDelRendimento', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATR_BF.ID_AREA AS Need_Area,
|
|
PATR_BF.ID_CONTRATTO AS Contratto,
|
|
CASE
|
|
WHEN PATR_BF.ID_AREA IN ('Ris', 'Ext', 'Liq') THEN AREA.NOME_AREA
|
|
ELSE ltrim(rtrim(PATR_BF.NOME_PROGETTO))
|
|
END AS Area_O_Nome_Progetto,
|
|
--PRODOTTI.DESCR_PRODOTTO AS Descrizione,
|
|
CASE WHEN PATR_BF.TIPO_PRODOTTO <> 'CC' THEN PRODOTTI.DESCR_PRODOTTO
|
|
ELSE PRODOTTI.DESCR_PRODOTTO+' '+SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC))
|
|
END AS Descrizione,
|
|
PATR_BF.PARTVIA_DISINV AS PartitaViaggiante,
|
|
SUM(PATR_BF.CTV) OVER (PARTITION BY PATR_BF.POSITION_ID) AS Controvalore,
|
|
dbo.ToShortDateString(PATR_BF.DATA_SOTTOSCRIZIONE) AS DataSottoscrizione
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF AS PATR_BF
|
|
INNER JOIN C6MartPeriodico.AREA_BISOGNO AS AREA ON
|
|
PATR_BF.ID_AREA = AREA.ID_AREA
|
|
INNER JOIN C6MartPeriodico.ANAG_PRODOTTI AS PRODOTTI ON
|
|
PATR_BF.COD_PRODOTTO = PRODOTTI.COD_PRODOTTO
|
|
--V La sezione deve uscire soltanto per aree monitorate
|
|
INNER JOIN C6MartPeriodico.MONITORAGGIO_ATTUALE M ON
|
|
PATR_BF.RETE = M.RETE AND PATR_BF.COD_FISCALE = M.COD_FISCALE
|
|
AND(
|
|
(PATR_BF.ID_AREA = M.ID_AREA AND PATR_BF.ID_AREA <> 'INV')
|
|
OR
|
|
(PATR_BF.ID_AREA = 'INV' AND PATR_BF.ID_AREA = M.ID_AREA AND PATR_BF.ORDINAMENTO_PROGETTO = M.ORDINAMENTO_PROGETTO)
|
|
)
|
|
LEFT OUTER JOIN C6MartPeriodico.ANAG_CC ON
|
|
ANAG_CC.COD_FISCALE = PATR_BF.COD_FISCALE
|
|
AND ANAG_CC.RETE = PATR_BF.RETE
|
|
AND cast(ANAG_CC.cod_cc as integer ) = substring(PATR_BF.position_id,72,9)
|
|
WHERE
|
|
PATR_BF.RETE = @Rete
|
|
AND PATR_BF.COD_FISCALE = @CodiceFiscale
|
|
AND PATR_BF.REND_NON_RAPPR = 1
|
|
ORDER BY
|
|
Area_O_Nome_Progetto
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S85RendimentoPortafoglioCono
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
SELECT '' as 'C6MartPeriodico.PL_S85RendimentoPortafoglioCono', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, [RETE]
|
|
,[COD_FISCALE]
|
|
,[ID_AREA]
|
|
,isnull([NOME_PROGETTO],'XXX') as NOME_PROGETTO
|
|
,[TIPO_PROGETTO]
|
|
,[PERC_AREA]
|
|
,[CTV_PROGETTO]
|
|
,[CTV_AREA]
|
|
,[ORIZZ_TEMP_NUM]
|
|
,[ORIZZ_TEMP]
|
|
,[CTV_TOTALE]
|
|
,[STATO_CONSULENZA]
|
|
,[DATA_STATO_CONS]
|
|
,[DATA_MIGR_CONS]
|
|
,[ID_CONTRATTO]
|
|
,[ORDINAMENTO_PROGETTO]
|
|
,[ID_ELAB]
|
|
,[TIPO_ELAB]
|
|
into #pir_mod_xxx
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[PIRAMIDE_MOD]
|
|
WHERE cod_fiscale = @CodiceFiscale AND rete = @rete
|
|
--select * from #pir_mod_xxx where cod_fiscale = 'FF@7181'
|
|
SELECT '' as 'C6MartPeriodico.PL_S85RendimentoPortafoglioCono', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
CONO.ID_AREA AS NEED_AREA,
|
|
CASE
|
|
WHEN CONO.NOME_PROGETTO = 'XXX' THEN ''
|
|
ELSE CONO.NOME_PROGETTO
|
|
END AS NOME_PROGETTO,
|
|
cono.ordinamento_progetto,
|
|
CONO.DATA_CONO,
|
|
CONO.SCENARIO_OTTIMISTICO AS OTTIMISTICO,
|
|
CONO.SCENARIO_PESSIMISTICO AS PESSIMISTICO,
|
|
isnull(PIRMOD.ORIZZ_TEMP_NUM,0) * 12 AS ORIZZONTE_MESI,
|
|
MONIT.RENDIMENTO_CUMULATO AS RENDIMENTO,
|
|
MONIT.DATA_CTV AS DATA_MONIT
|
|
FROM
|
|
C6MartPeriodico.CONO_PIANIFICAZIONE AS CONO
|
|
INNER JOIN C6MartPeriodico.MONITORAGGIO_ATTUALE AS MONIT
|
|
ON CONO.RETE = MONIT.RETE
|
|
AND CONO.COD_FISCALE = MONIT.COD_FISCALE
|
|
AND CONO.ID_AREA = MONIT.ID_AREA
|
|
AND CONO.NOME_PROGETTO = MONIT.NOME_PROGETTO
|
|
And cono.ordinamento_progetto=monit.ordinamento_progetto --l'ordinamento progetto è sempre pari alla chiaveprogetto e non idmonitoraggio
|
|
INNER JOIN #pir_mod_xxx as PIRMOD
|
|
ON CONO.RETE = PIRMOD.RETE
|
|
AND CONO.COD_FISCALE = PIRMOD.COD_FISCALE
|
|
AND CONO.ID_AREA = PIRMOD.ID_AREA
|
|
AND CONO.NOME_PROGETTO = PIRMOD.NOME_PROGETTO
|
|
And cono.ordinamento_progetto=pirmod.ordinamento_progetto
|
|
-- INNER JOIN C6MartPeriodico.PIRAMIDE_MOD AS PIRMOD
|
|
-- ON CONO.RETE = PIRMOD.RETE
|
|
-- AND CONO.COD_FISCALE = PIRMOD.COD_FISCALE
|
|
-- AND CONO.ID_AREA = PIRMOD.ID_AREA
|
|
-- AND CONO.NOME_PROGETTO = PIRMOD.NOME_PROGETTO
|
|
WHERE
|
|
CONO.RETE = @Rete AND
|
|
CONO.COD_FISCALE = @CodiceFiscale
|
|
--and DATA_CTV is not null
|
|
ORDER BY CONO.DATA_CONO
|
|
drop table #pir_mod_xxx
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S82DatiSintetici
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
/*DECLARE @TOTRischio DECIMAL(20,2)
|
|
SELECT
|
|
@TOTRischio = SUM(ISNULL(RISCHIOAREA.VAR_PERC_PTF,0) * PATRAREA.CTV)
|
|
FROM
|
|
C6MartPeriodico.vPatrimonioBFAggregatoPerArea AS PATRAREA INNER JOIN
|
|
C6MartPeriodico.RISCHIO_AGGREGATO AS RISCHIOAREA ON
|
|
PATRAREA.RETE = RISCHIOAREA.RETE AND
|
|
PATRAREA.COD_FISCALE = RISCHIOAREA.COD_FISCALE
|
|
WHERE
|
|
PATRAREA.ID_AREA IN ('RIS','EXT','LIQ','PRE','INV') AND
|
|
PATRAREA.RETE = @Rete AND
|
|
PATRAREA.COD_FISCALE = @CodiceFiscale*/
|
|
SELECT '' as 'C6MartPeriodico.PL_S82DatiSintetici', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRAREA.ID_AREA AS need_area,
|
|
ISNULL(RISCHIOAREA.VAR_PERC_PTF,0) AS var_needarea,
|
|
CASE
|
|
WHEN RISCHIOAREA.COPERTURA IS NULL THEN 'n.c.'
|
|
WHEN RISCHIOAREA.COPERTURA = 0 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS var_needareaString,
|
|
ISNULL(RISCHIOAREA.COPERTURA,0) AS copertura_needarea,
|
|
CASE
|
|
WHEN RISCHIOAREA.COPERTURA IS NULL THEN 'n.c.'
|
|
WHEN RISCHIOAREA.COPERTURA = 0 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS copertura_needareaString,
|
|
PATRAREA.ORDINAMENTO_PROGETTO,
|
|
/*V Calcolato a livello di codice
|
|
CASE
|
|
WHEN PATRAGGR.CTV = 0 THEN 0
|
|
ELSE PATRAREA.CTV / PATRAGGR.CTV * 100
|
|
END AS pesoPerc,*/
|
|
PATRAREA.CTV AS ControvaloreAttuale
|
|
/* Calcolato a livello di codice
|
|
CASE
|
|
WHEN @TOTRischio = 0 THEN 0
|
|
ELSE ISNULL(RISCHIOAREA.VAR_PERC_PTF,0) * PATRAREA.CTV / @TOTRischio * 100
|
|
END AS rischioRelativo*/
|
|
FROM C6MartPeriodico.vPatrimonioBFAggregatoPerArea AS PATRAREA
|
|
INNER JOIN C6MartPeriodico.vPatrimonioBFAggregato AS PATRAGGR
|
|
ON PATRAGGR.RETE = PATRAREA.Rete
|
|
AND PATRAGGR.COD_FISCALE = PATRAREA.COD_FISCALE
|
|
LEFT OUTER JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIOAREA
|
|
ON RISCHIOAREA.COD_AGGREG = 'RISFIN|PIRAMIDE|' + LEFT(PATRAREA.ID_AREA,3)
|
|
AND RISCHIOAREA.RETE = PATRAREA.Rete
|
|
AND RISCHIOAREA.COD_FISCALE = PATRAREA.COD_FISCALE
|
|
WHERE 1=1
|
|
AND PATRAREA.RETE = @Rete
|
|
AND PATRAREA.COD_FISCALE = @CodiceFiscale
|
|
AND PATRAREA.ID_AREA IN ('RIS','EXT','LIQ','PRE')
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S82BisDatiSintetici
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
/*DECLARE @TOTRischio DECIMAL(20,2)
|
|
SELECT
|
|
@TOTRischio = SUM(ISNULL(RISCHIOAREA.VAR_PERC_PTF,0) * PATRAREA.CTV)
|
|
FROM
|
|
C6MartPeriodico.vPatrimonioBFAggregatoPerArea AS PATRAREA INNER JOIN
|
|
C6MartPeriodico.RISCHIO_AGGREGATO AS RISCHIOAREA ON
|
|
PATRAREA.RETE = RISCHIOAREA.RETE AND
|
|
PATRAREA.COD_FISCALE = RISCHIOAREA.COD_FISCALE
|
|
WHERE
|
|
PATRAREA.ID_AREA IN ('RIS','EXT','LIQ','PRE','INV') AND
|
|
PATRAREA.RETE = @Rete AND
|
|
PATRAREA.COD_FISCALE = @CodiceFiscale*/
|
|
SELECT DISTINCT '' as 'C6MartPeriodico.PL_S82BisDatiSintetici', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
BF.ORDINAMENTO_PROGETTO,
|
|
'Inv' AS need_area,
|
|
ltrim(rtrim(ISNULL(PIRMOD.NOME_PROGETTO,BF.NOME_PROGETTO))) AS NOME_PROGETTO,
|
|
ISNULL(RA.VAR_PERC_PTF,0) AS var_needarea,
|
|
CASE
|
|
WHEN RA.COPERTURA IS NULL THEN 'n.c.'
|
|
WHEN RA.COPERTURA = 0 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS var_needareaString,
|
|
ISNULL(RA.COPERTURA,0) AS copertura_needarea,
|
|
CASE
|
|
WHEN RA.COPERTURA IS NULL THEN 'n.c.'
|
|
WHEN RA.COPERTURA = 0 THEN 'n.c.'
|
|
ELSE NULL
|
|
END AS copertura_needareaString,
|
|
PIRMOD.ORIZZ_TEMP AS orizzonte_Pianificato,
|
|
--C6MartPeriodico.getDescFromDate(DATEDIFF(m,(DATEADD(yy,PIRMOD.ORIZZ_TEMP_NUM,PIRMOD.DATA_PIANIF)) ,GETDATE()) + 1) AS durataResidua,
|
|
--DATEDIFF(m,DATEADD(yy,PIRMOD.ORIZZ_TEMP_NUM, PIRMOD.DATA_PIANIF) ,GETDATE()) + 1 AS durataResidua,
|
|
C6MartPeriodico.getDescFromDate(DATEDIFF(m,C6MartPeriodico.getTrimestre(GETDATE(),0),(DATEADD(yy,PIRMOD.ORIZZ_TEMP_NUM,PIRMOD.DATA_PIANIF)) )) AS durataResidua,
|
|
/*V Calcolato a livello di codice
|
|
CASE
|
|
WHEN BFAggr.CTV = 0 THEN 0
|
|
ELSE SUM(BF.CTV) OVER (PARTITION BY BF.RETE, BF.COD_FISCALE, BF.NOME_PROGETTO) / BFAggr.CTV * 100
|
|
END AS pesoPerc,*/
|
|
SUM(BF.CTV) OVER (PARTITION BY BF.RETE, BF.COD_FISCALE, BF.NOME_PROGETTO, bf.ordinamento_progetto) AS ControvaloreAttuale
|
|
/*V Calcolato a livello di codice
|
|
CASE
|
|
WHEN @TOTRischio = 0 THEN 0
|
|
ELSE ISNULL(RA.VAR_PERC_PTF,0) * SUM(BF.CTV) OVER (PARTITION BY BF.RETE, BF.COD_FISCALE, BF.NOME_PROGETTO) / @TOTRischio * 100
|
|
END AS rischioRelativo*/
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF AS BF
|
|
LEFT OUTER JOIN C6MartPeriodico.RISCHIO_AGGREGATO AS RA
|
|
ON BF.RETE = RA.RETE
|
|
AND BF.COD_FISCALE = RA.COD_FISCALE
|
|
--V AND RA.COD_AGGREG = 'PIRAMIDE|Inv'
|
|
AND RA.COD_AGGREG = 'RISFIN|PIRAMIDE|Inv|'+ BF.NOME_PROGETTO + '|' + cast(bf.ordinamento_progetto as varchar)
|
|
--
|
|
INNER JOIN C6MartPeriodico.PIRAMIDE_MOD AS PIRMOD
|
|
ON BF.RETE = PIRMOD.RETE
|
|
AND BF.COD_FISCALE = PIRMOD.COD_FISCALE
|
|
--AND BF.NOME_PROGETTO = PIRMOD.NOME_PROGETTO
|
|
AND BF.ORDINAMENTO_PROGETTO = PIRMOD.ORDINAMENTO_PROGETTO
|
|
AND PIRMOD.ID_AREA = 'Inv'
|
|
INNER JOIN C6MartPeriodico.vPatrimonioBFAggregato AS BFAggr
|
|
ON BF.RETE = BFAggr.RETE
|
|
AND BF.COD_FISCALE = BFAggr.COD_FISCALE
|
|
LEFT OUTER JOIN C6MARTPERIODICO.MONITORAGGIO_ATTUALE AS MONIT
|
|
ON BF.RETE = MONIT.RETE
|
|
AND BF.COD_FISCALE = MONIT.COD_FISCALE
|
|
AND BF.ID_AREA = MONIT.ID_AREA
|
|
AND BF.NOME_PROGETTO = MONIT.NOME_PROGETTO
|
|
AND BF.ORDINAMENTO_PROGETTO = MONIT.ORDINAMENTO_PROGETTO
|
|
WHERE 1=1
|
|
AND BF.ID_AREA = 'Inv'
|
|
AND BF.RETE = @Rete
|
|
AND BF.COD_FISCALE = @CodiceFiscale
|
|
--AND BF.ORDINAMENTO_PROGETTO=1521667
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S76AlternativaPatrimonioNonRappresentabileAssettClass
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
DECLARE @NOMERETE2 VARCHAR(20)
|
|
IF @RETE = 'F'
|
|
SET @NOMERETE2 = 'Banca Fideuram'
|
|
ELSE
|
|
SET @NOMERETE2 = 'Sanpaolo Invest'
|
|
SELECT '' as 'C6MartPeriodico.PL_S76AlternativaPatrimonioNonRappresentabileAssettClass', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRBF.ID_CONTRATTO as Contratto,
|
|
CASE WHEN AREA.NOME_AREA LIKE 'Previdenza%' THEN 'Previdenza' ELSE AREA.NOME_AREA END as Area,
|
|
CASE
|
|
WHEN area.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
|
|
ELSE area.ID_AREA end as NEEDAREA,
|
|
PATRBF.NOME_PROGETTO as Progetto,
|
|
patrbf.chiave_progetto as ord_progetto,
|
|
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) as DataSottoscrizione,
|
|
ANAGP.DESCR_PRODOTTO AS Descrizione,
|
|
PATRBF.CTV as Controvalore,
|
|
PATRBF.CTV as controvalore_contratto,
|
|
PATRBF.PARTVIA_DISINV as PartitaViaggiante,
|
|
@NOMERETE2 as Intermediario
|
|
--patrbf.ordinamento_progetto
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN
|
|
C6MartPeriodico.AREA_BISOGNO AREA
|
|
ON
|
|
PATRBF.ID_AREA = AREA.ID_AREA
|
|
INNER JOIN
|
|
C6MartPeriodico.ANAG_PRODOTTI ANAGP
|
|
ON
|
|
PATRBF.COD_PRODOTTO = ANAGP.COD_PRODOTTO
|
|
LEFT OUTER JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
PATRBF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
WHERE 1 = 1
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
AND PATRBF.TIPO_PRODOTTO <> 'ASUL'
|
|
AND ASSETPERC.COD_PRODOTTO IS NULL
|
|
--INIZIO INTERVENTI OMNIA
|
|
AND PATRBF.CTV <> 0.00
|
|
UNION ALL
|
|
SELECT '' as 'C6MartPeriodico.PL_S76AlternativaPatrimonioNonRappresentabileAssettClass', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale,
|
|
PATRBF.ID_CONTRATTO as Contratto,
|
|
CASE WHEN AREA.NOME_AREA LIKE 'Previdenza%' THEN 'Previdenza' ELSE AREA.NOME_AREA END as Area,
|
|
CASE
|
|
WHEN area.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
|
|
ELSE area.ID_AREA end as NEEDAREA,
|
|
PATRBF.NOME_PROGETTO as Progetto,
|
|
patrbf.chiave_progetto as ord_progetto,
|
|
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) as DataSottoscrizione,
|
|
ANAGP.DESCR_PRODOTTO AS Descrizione,
|
|
sum(ASUL.CTV) as Controvalore,
|
|
sum(ASUL.CTV) as controvalore_contratto,
|
|
Max(PATRBF.PARTVIA_DISINV) as PartitaViaggiante,
|
|
@NOMERETE2 as Intermediario
|
|
--patrbf.ordinamento_progetto
|
|
FROM
|
|
C6MartPeriodico.PATRIMONIO_BF PATRBF
|
|
INNER JOIN
|
|
C6MartPeriodico.AREA_BISOGNO AREA
|
|
ON
|
|
PATRBF.ID_AREA = AREA.ID_AREA
|
|
INNER JOIN
|
|
C6MartPeriodico.ANAG_PRODOTTI ANAGP
|
|
ON
|
|
PATRBF.COD_PRODOTTO = ANAGP.COD_PRODOTTO
|
|
INNER JOIN
|
|
C6MartPeriodico.DETTAGLIO_ASUL ASUL
|
|
ON
|
|
PATRBF.RETE = ASUL.RETE
|
|
AND PATRBF.COD_FISCALE = ASUL.COD_FISCALE
|
|
AND PATRBF.ID_CONTRATTO = ASUL.ID_CONTRATTO
|
|
left JOIN
|
|
C6MartPeriodico.ASSET_PERC ASSETPERC
|
|
ON
|
|
(
|
|
ASSETPERC.COD_PRODOTTO = ASUL.COD_PRODOTTO
|
|
---no SKANDIA
|
|
AND ASUL.COD_ISIN_SOTT = ''
|
|
AND ASSETPERC.LIVELLO = 1
|
|
)
|
|
or
|
|
( --SKANDIA
|
|
ASSETPERC.COD_ISIN = ASUL.COD_ISIN_SOTT
|
|
AND ASSETPERC.COD_MAF = ASUL.COD_MAF_SOTT
|
|
AND ASSETPERC.COD_INTERNO = ASUL.COD_INTERNO
|
|
AND rtrim(ASSETPERC.COD_SOTTOPRODOTTO) = ASUL.COD_SOTTOPRODOTTO
|
|
AND ASSETPERC.LIVELLO = 1
|
|
)
|
|
WHERE
|
|
ASSETPERC.COD_PRODOTTO IS NULL
|
|
AND PATRBF.TIPO_PRODOTTO = 'ASUL'
|
|
AND PATRBF.RETE = @Rete
|
|
AND PATRBF.COD_FISCALE = @CodiceFiscale
|
|
GROUP BY
|
|
PATRBF.ID_CONTRATTO,
|
|
CASE WHEN AREA.NOME_AREA LIKE 'Previdenza%' THEN 'Previdenza' ELSE AREA.NOME_AREA END,
|
|
CASE
|
|
WHEN area.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
|
|
ELSE area.ID_AREA end,
|
|
PATRBF.NOME_PROGETTO,
|
|
patrbf.chiave_progetto,
|
|
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE),
|
|
ANAGP.DESCR_PRODOTTO
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_MP_S149DettaglioProdottiAffluent
|
|
BEGIN
|
|
SELECT '' as 'C6MartPeriodico.PL_MP_S149DettaglioProdottiAffluent', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, ISNULL(SUM(ctv),0)
|
|
FROM C6MartPeriodico.PATRIMONIO_BF
|
|
WHERE RETE = @Rete
|
|
AND COD_FISCALE = @CodiceFiscale
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_S80Alternativa
|
|
BEGIN
|
|
DECLARE @MONIT_ATTIV INT
|
|
DECLARE @MONIT_INIT INT
|
|
SET @MONIT_ATTIV=0
|
|
SET @MONIT_INIT = 0
|
|
-- INSERIMENTO BLOCCO PER MONITORAGGIO CHIUSO O NON ESISTENTE
|
|
SELECT @MONIT_ATTIV=COUNT(*)
|
|
FROM
|
|
C6MARTPERIODICO.MONITORAGGIO_ATTUALE
|
|
WHERE
|
|
RETE = @Rete AND
|
|
COD_FISCALE = @CodiceFiscale
|
|
SELECT @MONIT_INIT = COUNT(*)
|
|
FROM
|
|
C6MARTPERIODICO.MONITORAGGIO_INIZIALE
|
|
WHERE
|
|
RETE = @Rete AND
|
|
COD_FISCALE = @CodiceFiscale
|
|
IF (@MONIT_ATTIV > 0 AND @MONIT_INIT > 0)
|
|
BEGIN
|
|
SELECT '' as 'C6MartPeriodico.PL_S80Alternativa', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, [RETE]
|
|
,[Cod_Fiscale]
|
|
,[Fase]
|
|
,[DataFase]
|
|
,[Partita_viaggiante]
|
|
,[CTV_EXT]
|
|
,[CTV_INV]
|
|
,[CTV_PRE]
|
|
,[CTV_RIS]
|
|
,[CTV_LIQ]
|
|
,[RisorseAllocate]
|
|
,[RisorseNonAllocate]
|
|
,[RisorseFinanziarie]
|
|
,[Contocorrente]
|
|
,[ctvself]
|
|
,[TotalePatrimonio]
|
|
,[DettMovS80] DettMov
|
|
,[NumeroAsterischiNota]
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[TB_S80_S129_EvoluzioneCTVPatrimonio]
|
|
WHERE [Rete] = @Rete
|
|
AND [Cod_Fiscale] = @CodiceFiscale
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT '' as 'C6MartPeriodico.PL_S80Alternativa', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, [RETE]
|
|
,[Cod_Fiscale]
|
|
,[Fase]
|
|
,[DataFase]
|
|
,[Partita_viaggiante]
|
|
,[CTV_EXT]
|
|
,[CTV_INV]
|
|
,[CTV_PRE]
|
|
,[CTV_RIS]
|
|
,[CTV_LIQ]
|
|
,[RisorseAllocate]
|
|
,[RisorseNonAllocate]
|
|
,[RisorseFinanziarie]
|
|
,[Contocorrente]
|
|
,[ctvself]
|
|
,[TotalePatrimonio]
|
|
,[DettMovS80] DettMov
|
|
,[NumeroAsterischiNota]
|
|
FROM [C6StampeCentralizzate].[C6MartPeriodico].[TB_S80_S129_EvoluzioneCTVPatrimonio]
|
|
WHERE
|
|
1=2
|
|
END
|
|
END
|
|
---------------------------------------------------------------
|
|
---------------------------------------------------------------C6MartPeriodico.PL_D_S331AreeBisogno
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
SELECT '' as 'C6MartPeriodico.PL_D_S331AreeBisogno', @Rete as i_rete ,@CodiceFiscale as i_codiceFiscale, [NeedArea]
|
|
,[AreaName]
|
|
,[ControvaloreAttuale]
|
|
,Controvaloremodello as [ControvaloreProtetto]
|
|
,totale_percentuale as [ContributoProtezione]
|
|
FROM [C6MartPeriodico].[TB_S133_S157DatiPiramide]
|
|
--FROM [C6StampeCentralizzate3112].[C6MartPeriodico].[TB_S331_DatiPiramide]
|
|
WHERE [Rete] = @Rete
|
|
AND [CodiceFiscale] = @CodiceFiscale
|
|
order by ordinamento
|
|
END
|
|
---------------------------------------------------------------
|
|
end |