-- =============================================
-- AUTHOR:		ALESSANDRO TRINGALI
-- CREATE DATE: 18 SETTEMBRE 2009
-- DESCRIPTION:	
-- =============================================
CREATE procedure [C6MartPeriodico].[DM_ALL_PATRIMONIO]
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ID_ELAB INT
	SET @ID_ELAB = [C6MARTPERIODICO].GETIDELAB()
	-- LOG ESECUZIONE
	INSERT INTO LOG_ESECUZIONE
		(NOME, INIZIO, NOTA)
	VALUES (
		'DM_ALL_PATRIMONIO',
		GETDATE(),
		'ELABORAZIONE TRIMESTRALE'
	)
	TRUNCATE TABLE [C6MartPeriodico].[ALL_PATRIMONIO] 
	INSERT INTO [C6MARTPERIODICO].[ALL_PATRIMONIO]
           (
           [CODICE_FISCALE]
           ,[RETE]
           ,[POSITIONID]
           ,[CATALOGUENAME]
           ,[CATALOGUEID]
           ,[COD_ADEGUATEZZA]
           ,[INTERMEDIARIO]
           ,[PATRIMONIO_INTERMEDIARIO]
           ,[EMITTENTE]
           ,[PATRIMONIO_EMITTENTE]
           ,[COMPLESSITA]
           ,[TIPO_PRODOTTO]
           ,[CONTROVALORE_PRODOTTO]
           ,[AZ_OB]
           ,[PATRIMONIO_AZ_OB_EMIT]
           ,[PATRIM_AZ_OB_EMIT_INTER]   
           ,[PATRIMONIO_COMPLESSIVO] 
           ,[CHIAVE_THR]
           )
SELECT 
       CODICE_FISCALE,
       RETE,
       POSITIONID,
       DATI.CATALOGUENAME,
       CAT.CATALOGUEID,
       CASE
            WHEN ADEGUATEZZA IS NULL
                 THEN CAT.COD_ADEGUATEZZA
            ELSE ADEGUATEZZA
       END AS COD_ADEGUATEZZA,
       INTERMEDIARIO,
       SUM(CONTROVALORE) OVER (PARTITION BY CODICE_FISCALE,RETE,INTERMEDIARIO) AS PATRIMONIO_INTERMEDIARIO,
       EMIT.DESCR AS EMITTENTE,
       CASE
            WHEN EMIT.DESCR IS NULL THEN NULL
            ELSE SUM(CONTROVALORE) OVER (PARTITION BY CODICE_FISCALE,RETE,EMIT.DESCR)
       END AS PATRIMONIO_EMITTENTE,
       CAT.COMPLEX AS COMPLESSITA,
       CAT.TIPOPRODOTTO,
       CONTROVALORE  AS CONTROVALORE_PRODOTTO,
       CASE WHEN CAT.STRTYPE IS NULL
            THEN AZ_OB
            ELSE CAT.STRTYPE
       END AS AZ_OB,
       SUM(CONTROVALORE) OVER (PARTITION BY CODICE_FISCALE,RETE,CASE WHEN CAT.STRTYPE IS NULL THEN AZ_OB ELSE CAT.STRTYPE END,EMIT.DESCR) AS PATRIMONIO_AZ_OB_EMIT,
       SUM(CONTROVALORE) OVER (PARTITION BY CODICE_FISCALE,RETE,INTERMEDIARIO,CASE WHEN CAT.STRTYPE IS NULL THEN AZ_OB ELSE CAT.STRTYPE END,EMIT.DESCR, POSITIONID) AS PATRIM_AZ_OB_EMIT_INTER,
       SUM(CONTROVALORE) OVER (PARTITION BY CODICE_FISCALE,RETE) AS PATRIMONIO_COMPLESSIVO,
       CHIAVE_THR
  FROM (
       SELECT NULL AS AGENTE,
               C.RETE,
               C.CODICE_FISCALE,
               C.CATALOGUENAME,
               C.CONTROVALORE,
               'BF' AS INTERMEDIARIO,
               SUBSTRING(C.STRINGONA_POS, 1, 57) AS CATALOGUEID,
               C.STRINGONA_POS AS POSITIONID,
               NULL AS ADEGUATEZZA,
               'BF' AS PROVENIENZA,
               NULL AZ_OB,
               '' AS CHIAVE_THR
          FROM C6STAGINGPERIODICO.ALL_CONSUL_ASUL C
        union all
        SELECT agente,
               substring(agente,1,1) as rete,
               codicefiscale as codice_FISCALE,
               nome_prodotto as cataloguename,
               SUM(controvalore) AS controvalore,
               intermediario,
				case when codiceisin =  '' then '############' else  codiceisin + replicate(' ', 12-len(codiceisin))  end
				 +
				case when codicemaf =  '' then '###############' else  codicemaf + replicate(' ', 15-len(codicemaf))   end
				+
				case when codiceinterno =  '' then '###############' else  codiceinterno + replicate(' ', 15-len(codiceinterno))   end
				+
				case when codicesottoprodotto =  '' then '###############' else  codicesottoprodotto + replicate(' ', 15-len(codicesottoprodotto))   end
				 as catalogueid,
                      null as positionid,
                      codiceadeguatezza as ADEGUATEZZA ,
                      'PPT' as provenienza,
                      --alberatura,
                      case
                          when substring( alberatura,1,13)='Amm.Titoli.Ob'
                               OR substring( alberatura,1,15)='Amm.Strutturati'
                          then 'OB'
                          when substring( alberatura,1,17)='Amm.Titoli.Azioni'
                               OR substring( alberatura,1,18)='Amm.Titoli.Diritti'
                               OR substring( alberatura,1,20)='Amm.Derivati.Warrant'
                               OR substring( alberatura,1,19)='Amm.Derivati.CovWar'
                          then 'AZ'
                          else null
                       end as az_ob, 
                       CHIAVE_THR
          FROM (SELECT cf_e_pb.agente AS agente,
                       cf_e_pb.codice_fiscale AS codicefiscale,
                       nome_istituto AS intermediario,
                       SUM(paTRIMonio) AS controvalore,
                       REPLACE(codice_isin, '$', '') AS codiceisin,
                       REPLACE(codice_adeguatezza, '$', '') AS codiceadeguatezza,
                       REPLACE(codice_maf, '$', '') AS codicemaf,
                       REPLACE(codice_interno, '$', '') AS codiceinterno,
                       REPLACE(codice_sottoprodotto,
                              '$',
                              ''    ) AS codicesottoprodotto,
                       CASE
                         WHEN traduz_alberat.albero_gerarchia_3 =
                              'Conti correnti' AND
                              tipo_prodotto =
                              'Assets.FinancialAssets.CurrentAccount' THEN
                          'Cc'
                         ELSE
                          'xxx'
                       END AS need_si_o_no_cc,
                       provenienza,
                       CASE
                         WHEN provenienza = 'dati NON in catalogo' THEN
                          1
                         ELSE
                          0
                       END AS prodotti_non_in_catalogo,
                       tipo_prodotto,
                       nome_prodotto,
                       alberatura,
                       CHIAVE_THR
                  FROM (
                        -- asset per dati NON in catalogo
                        SELECT aa.paTRIMonio,
                                aa.chiave_cliente,
                                aa.nome_istituto,
                                bb.codice_isin,
                                bb.codice_adeguatezza,
                                bb.codice_maf,
                                NULL AS codice_interno,
                                NULL AS codice_sottoprodotto,
                                aa.alberatura,
                                aa.provenienza,
                                tipo_prodotto,
                                nomeprodotto AS nome_prodotto,
                                CHIAVE_THR
                          FROM (SELECT patr_tp.paTRIMonio_terzi *
                                        acb.bdpercentage AS paTRIMonio,
                                        patr_tp.chiave_per_cliente AS chiave_cliente,
                                        patr_tp.intermediario AS nome_istituto,
                                        ass.assetclassid,
                                        substring(patr_tp.descrizione_alberatura,
                                               1,
                                               45) AS alberatura,
                                        'dati NON in catalogo' AS provenienza,
                                        patr_tp.descrizione_prodotto AS tipo_prodotto,
                                        patr_tp.nomeprodotto,
                                        CHIAVE_THR
                                   FROM c6stagingperiodico.All_patr_terzi_senza_asset patr_tp,
                                        c6stagingperiodico.acbdenoth                    acb,
                                        c6stagingperiodico.assetclass                   ass
                                  WHERE ass.assetclasslev = 2
                                    AND patr_tp.prodotto_in_catalogo IS NULL
                                    AND patr_tp.chiave_fnc IS NOT NULL
                                    AND acb.f_pfinancial_815c0 = patr_tp.chiave_fnc
                                    AND acb.f_oid$_passe_cdf1a = ass.oid$
                                    ) aa,
                                c6stagingperiodico.all_asset_class_per_promet bb
                         WHERE bb.codice_asset_class_ingresso = aa.assetclassid
                        UNION ALL
                        -- asset per dati in catalogo che hanno cod_adeguatezza valorizzato
                        SELECT patr_tp.paTRIMonio_terzi AS paTRIMonio,
                               patr_tp.chiave_per_cliente AS chiave_cliente,
                               patr_tp.intermediario AS nome_istituto,
                               cat.cod_isin,
                               cat.cod_adeguatezza,
                               cat.cod_maf,
                               cat.cod_interno,
                               cat.cod_sottoprodotto,
                               substring(cat.type$_bcatal_fc317, 64, 45) AS alberatura,
                               'dati in catalogo che hanno cod_adeguatezza valorizzato' AS provenienza,
                               patr_tp.descrizione_prodotto AS tipo_prodotto,
                               patr_tp.nomeprodotto,
								CHIAVE_THR
                          FROM c6stagingperiodico.all_patr_terzi_senza_asset patr_tp,
                               c6stagingperiodico.catitemref                   cat
                         WHERE patr_tp.chiave_fnc IS NOT NULL
                           AND cat.cod_adeguatezza != '$'
                           AND patr_tp.prodotto_in_catalogo = cat.catalogueid
                        UNION ALL
                        SELECT a.paTRIMonio,
                               a.chiave_cliente,
                               a.nome_istituto,
                               b.codice_isin,
                               b.codice_adeguatezza,
                               b.codice_maf,
                               NULL AS codice_interno,
                               NULL AS codice_sottoprodotto,
                               a.alberatura,
                               a.provenienza,
                               tipo_prodotto,
                               nomeprodotto AS nome_prodotto,
                               CHIAVE_THR
                          FROM (
                                -- ASSET ESISTENTI PER DATI DEL CATALOGO CON ADEGUATEZZA A NULL
                                SELECT PATR_TP.PATRIMONIO_TERZI *
                                        ACB.BDPERCENTAGE AS PATRIMONIO,
                                        PATR_TP.CHIAVE_PER_CLIENTE AS CHIAVE_CLIENTE,
                                        PATR_TP.INTERMEDIARIO AS NOME_ISTITUTO,
                                        LTRIM(ASS.ASSETCLASSID) AS ASSETCLASSID,
                                        ASS.ASSETCLASSLEV,
                                        SUBSTRING(CAT.TYPE$_BCATAL_FC317, 64, 45) AS ALBERATURA,
                                        'DATI IN CATALOGO CHE HANNO COD_ADEGUATEZZA A NULL E ASSET VALORIZZATO' AS PROVENIENZA,
                                        PATR_TP.DESCRIZIONE_PRODOTTO AS TIPO_PRODOTTO,
                                        PATR_TP.NOMEPRODOTTO,
                                        patr_tp.CHIAVE_THR
                                  FROM  C6STAGINGPERIODICO.ASSETCLASS                   ASS,
                                        C6STAGINGPERIODICO.ALL_PATR_TERZI_SENZA_ASSET PATR_TP,
                                        C6STAGINGPERIODICO.CATITEMREF                   CAT,
                                        C6STAGINGPERIODICO.INSTRBDS                     INS,
                                        C6STAGINGPERIODICO.ACBDENTRY                    ACB
                                 WHERE CAT.COD_ADEGUATEZZA = '$'
                                   AND ASS.ASSETCLASSLEV = 2
                                   AND PATR_TP.PRODOTTO_IN_CATALOGO = CAT.CATALOGUEID
                                   AND PATR_TP.CHIAVE_FNC IS NOT NULL
                                   AND CAT.OID$ = INS.F_OID$_PCATA_5DDA6
                                   AND INS.OID$ = ACB.F_PINSTRUMEN_FBCDE
                                   AND ACB.F_OID$_PASSE_B8A1E = ASS.OID$
                                UNION ALL
                                -- ASSET NON ESISTENTI PER DATI DEL CATALOGO CON ADEGUATEZZA A NULL (SETTATI AD ALTRO PER CONVENZIONE)
                                SELECT PATR_TP.PATRIMONIO_TERZI AS PATRIMONIO,
                                       PATR_TP.CHIAVE_PER_CLIENTE AS CHIAVE_CLIENTE,
                                       PATR_TP.INTERMEDIARIO AS NOME_ISTITUTO,
                                       'AL1' AS ASSETCLASSID,
                                       2,
                                       SUBSTRING(CAT.TYPE$_BCATAL_FC317, 64, 45) AS ALBERATURA,
                                       'DATI IN CATALOGO CHE HANNO COD_ADEGUATEZZA A NULL E ASSET NON VALORIZZATO',
                                       PATR_TP.DESCRIZIONE_PRODOTTO AS TIPO_PRODOTTO,
                                       PATR_TP.NOMEPRODOTTO,
                                       	patr_tp.CHIAVE_THR
                                  FROM C6STAGINGPERIODICO.ALL_PATR_TERZI_SENZA_ASSET PATR_TP,
                                       C6STAGINGPERIODICO.CATITEMREF                   CAT,
                                       C6STAGINGPERIODICO.INSTRBDS                     INS
                                 WHERE CAT.COD_ADEGUATEZZA = '$'
                                   AND PATR_TP.CHIAVE_FNC IS NOT NULL
                                   AND PATR_TP.PRODOTTO_IN_CATALOGO = CAT.CATALOGUEID
                                   AND CAT.OID$ = INS.F_OID$_PCATA_5DDA6  --(+)
                                   AND INS.F_OID$_PCATA_5DDA6 IS NULL
                                ) A,
                               C6STAGINGPERIODICO.ALL_ASSET_CLASS_PER_PROMET B
                         WHERE B.CODICE_ASSET_CLASS_INGRESSO = A.ASSETCLASSID
                         ) patrimonio_PT,
                       C6STAGINGPERIODICO.ALL_CF_PB CF_E_PB,
                       C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3 TRADUZ_ALBERAT
                 WHERE CF_E_PB.OID = patrimonio_PT.CHIAVE_CLIENTE
                   AND TRADUZ_ALBERAT.DESCRIZIONE_ORIGINALE = patrimonio_PT.ALBERATURA
                 GROUP BY CF_E_PB.AGENTE,
                          CF_E_PB.CODICE_FISCALE,
                          NOME_ISTITUTO,
                          REPLACE(codice_isin, '$', ''),
                          REPLACE(codice_adeguatezza,
                                 '$',
                                 ''
                                 ),
                          REPLACE(codice_maf, '$', ''),
                          REPLACE(codice_interno, '$', ''),
                          REPLACE(codice_sottoprodotto,
                                 '$',
                                 ''
                                 ),
                          PROVENIENZA,
                          TRADUZ_ALBERAT.ALBERO_GERARCHIA_3,
                          TIPO_PRODOTTO,
                          NOME_PRODOTTO,
                          ALBERATURA,
                          CHIAVE_THR) TUTTO_PPT
         GROUP BY AGENTE,
                  CODICEFISCALE,
                  INTERMEDIARIO,
                  NOME_PRODOTTO,
                  CODICEISIN,
                  CODICEADEGUATEZZA,
                  CODICEMAF,
                  CODICEINTERNO,
                  CODICESOTTOPRODOTTO,
                  PRODOTTI_NON_IN_CATALOGO,
                  ALBERATURA,
                  CHIAVE_THR
                  ) DATI,
       C6STAGINGPERIODICO.CATITEMREF CAT,
       C6STAGINGPERIODICO.EMIT
 WHERE CAT.CATALOGUEID = DATI.CATALOGUEID
   AND CAT.CODEMI = EMIT.CODEMI
	-- LOG ESECUZIONE
	UPDATE
		LOG_ESECUZIONE
	SET
		FINE = GETDATE(),
		TIPO = 'INSERT',
		RIGHE = @@ROWCOUNT
	WHERE
		NOME = 'DM_ALL_PATRIMONIO' AND
		INIZIO = (
			SELECT
				MAX(INIZIO)
			FROM
				LOG_ESECUZIONE
			WHERE
				NOME = 'DM_ALL_PATRIMONIO'
		)
END