-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE procedure [C6MartPeriodico].[DM_PATRIMONIO_TERZI_pre_selective]
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ID_ELAB INT
	DECLARE @TIPO_ELAB VARCHAR(1)
	SET @ID_ELAB = C6MartPeriodico.GETIDELAB()
	SET @TIPO_ELAB = C6MartPeriodico.GETTIPOELAB()
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'DM_PATRIMONIO_TERZI_pre_selective',
		GETDATE(),
		'Elaborazione trimestrale'
	)
	TRUNCATE TABLE C6MartPeriodico.PATRIMONIO_TERZI
	INSERT INTO C6MartPeriodico.[PATRIMONIO_TERZI]
           ([RETE]
           ,[COD_FISCALE]
           ,[NUM_POLIZZA]
           ,[TIPO_POLIZZA]
           ,[TIPO_PRODOTTO]
           ,[TIPO_PRODOTTO2]
           ,[COD_PRODOTTO]
           ,[COD_PRODOTTO_TERZI]
           ,[INTERMEDIARIO]
           ,[DATA_SOTTOSCRIZIONE]
           ,[DATA_SCADENZA]
           ,[FREQ_RATA]
           ,[DURATA_POLIZZA]
           ,[PREMIO]
           ,[ALIQUOTA]
           ,[DATA_SOTT_FP]
           ,[DATA_REGOLAMENTO]
           ,[QUANTITA]
           ,[PREZZO]
           ,[CTV]
           ,[VERSATO_NETTO]
           ,[DIV_REGOLAMENTO]
           ,[CAMBIO]
           ,[DESCR_GRUPPO_PRODOTTO]
           ,[STRTYPE]
           ,[COMPLEX]
           ,[EMITTENTE]
           ,[CHIAVE_THR]
           ,[ID_ELAB]
           ,[TIPO_ELAB]
)
	SELECT 
		ALL_PATR_TERZI.RETE_PB AS RETE,
		ALL_PATR_TERZI.CODICE_FISCALE AS COD_FISCALE,
		NULL AS NUM_POLIZZA,
		ALL_PATR_TERZI.TIPO_POLIZZA_BT_97BEE AS TIPO_POLIZZA,
		--COALESCE(PRODOTTI_A_CATALOGO.TIPO_PRODOTTO, PRODOTTI_NON_A_CATALOGO.TIPO_PRODOTTO) AS TIPO_PRODOTTO,   
		CASE
			WHEN ALL_PATR_TERZI.DESCRIZIONE_PRODOTTO = 'Assets.FinancialAssets.Other' AND S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_3 = 'Conti correnti'
				THEN 'Altro'
			ELSE
				COALESCE(S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_3,COALESCE(S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2,S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_1)) 
		END AS TIPO_PRODOTTO,
		PRODOTTI_NON_A_CATALOGO.TIPO_PRODOTTO2 AS TIPO_PRODOTTO2,   
		ISNULL(ALL_PATR_TERZI.PRODOTTO_IN_CATALOGO,'PRODOTTO NON IN CATALOGO') AS COD_PRODOTTO,
		ISNULL(ALL_PATR_TERZI.CHIAVE_THR,'PRODOTTO IN CATALOGO') AS COD_PRODOTTO_TERZI,
		ALL_PATR_TERZI.NOME_ISTITUTO AS INTERMEDIARIO,
		CASE 
			WHEN ALL_PATR_TERZI.DESCRIZIONE_PRODOTTO IN ('Assets.FinancialAssets.PolizzeRivalutabili')		-- PRODOTTI S49
				THEN ISNULL(ALL_PATR_TERZI.DATA_SOTTOSCRIZIONE, ALL_PATR_TERZI.DATA2_UTILIZ_X_SOTTOSCRIZ)
			WHEN ALL_PATR_TERZI.DESCRIZIONE_PRODOTTO IN ('Assets.FinancialAssets.ProdottiPrevidenziali')	-- PRODOTTI S50
				THEN ALL_PATR_TERZI.BALANCEDATE_DI_THR -- EQUIVALENTE A ALL_PATR_TERZI.DATA2_UTILIZ_X_SOTTOSCRIZ
			ELSE	-- PRODOTTI S48
				ALL_PATR_TERZI.DATA_INIZIO_INV
		END  
 AS DATA_SOTTOSCRIZIONE,
		ALL_PATR_TERZI.DATA_SCADENZA 
AS DATA_SCADENZA,
		ALL_PATR_TERZI.TIPO_VERSAMENTO_BT_6797A AS FREQ_RATA,
		NULL AS DURATA_POLIZZA,
		ALL_PATR_TERZI.PREMIO_VERSAMENTO AS PREMIO,
		NULL AS ALIQUOTA,
		ALL_PATR_TERZI.BALANCEDATE_DI_THR AS DATA_SOTT_FP,
		NULL AS DATA_REGOLAMENTO,
		NULL AS QUANTITA,
		NULL AS PREZZO,
		ALL_PATR_TERZI.PATRIMONIO_TERZI AS CTV,
		ALL_PATR_TERZI.CAPITALE_INVESTITO AS VERSATO_NETTO,
		NULL AS DIV_REGOLAMENTO,
		NULL AS CAMBIO,
		CASE 
			WHEN C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2 = 'Fondi' THEN C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2
			WHEN C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2 = 'Gestioni patrimoniali' THEN C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2
			WHEN (C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_1 = 'Amministrato' 
					AND ALL_PATR_TERZI.DESCRIZIONE_PRODOTTO NOT IN( 'Assets.FinancialAssets.CurrentAccount', 'Assets.FinancialAssets.Other'))
				THEN 'Prodotti di Tipo amministrato (escluso C/C)'
			WHEN (C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_3 IN ('Index linked', 'Unit linked')
                   or (S4_S5_S13_TRAD_UNTIL_LIV3.albero_gerarchia_1='Gestito Assicurativo previdenziale' 
                     and  S4_S5_S13_TRAD_UNTIL_LIV3.albero_gerarchia_2='Assicurativi Vita' 
                     and S4_S5_S13_TRAD_UNTIL_LIV3.albero_gerarchia_3 is null))
				THEN 'Assicurativi Unit/Index Linked'
			WHEN ALL_PATR_TERZI.DESCRIZIONE_PRODOTTO = 'Assets.FinancialAssets.Other'  THEN 'Portafoglio Generico'
			WHEN ALL_PATR_TERZI.DESCRIZIONE_PRODOTTO IN ('Assets.FinancialAssets.ProdottiPrevidenziali', 'Assets.FinancialAssets.PolizzeRivalutabili')  THEN NULL
			ELSE NULL 
		END AS DESCR_GRUPPO_PRODOTTO,
        STRTYPE,
        COMPLEX,
        EMITTENTE,    
        ALL_PATR_TERZI.CHIAVE_THR, 
		@ID_ELAB AS ID_ELAB,
		@TIPO_ELAB AS TIPO_ELAB
FROM 
		c6stagingperiodico.all_patr_terzi as ALL_PATR_TERZI--C6STAGINGPERIODICO.ALL_PATR_TERZI C6STAGINGPERIODICO.ALL_PATR_TERZI
	INNER JOIN 
		C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3 
			ON S4_S5_S13_TRAD_UNTIL_LIV3.DESCRIZIONE_ORIGINALE = ALL_PATR_TERZI.DESCRIZIONE_ALBERATURA
	LEFT JOIN (
		SELECT DISTINCT 
			ALL_PATR_TERZI.CHIAVE_THR AS CHIAVE_THR,
			COALESCE(S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_3,COALESCE(S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2,S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_1)) AS TIPO_PRODOTTO,
			S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2 AS TIPO_PRODOTTO2
		FROM 
			c6stagingperiodico.all_patr_terzi as ALL_PATR_TERZI --C6STAGINGPERIODICO.ALL_PATR_TERZI
		INNER JOIN 
			C6STAGINGPERIODICO.S4_S5_S13_TRAD_UNTIL_LIV3 
				ON S4_S5_S13_TRAD_UNTIL_LIV3.DESCRIZIONE_ORIGINALE = ALL_PATR_TERZI.DESCRIZIONE_ALBERATURA
		) PRODOTTI_NON_A_CATALOGO 
			ON PRODOTTI_NON_A_CATALOGO.CHIAVE_THR = ALL_PATR_TERZI.CHIAVE_THR
	LEFT JOIN (
		SELECT 
			CATITEMREF.CATALOGUEID AS CATALOGUEID,
			CATITEMREF.TIPOPRODOTTO AS TIPO_PRODOTTO,
            CATITEMREF.STRTYPE,
            CATITEMREF.COMPLEX,
            MIT.DESCR AS EMITTENTE
		FROM 
			C6STAGINGPERIODICO.CATITEMREF  LEFT OUTER JOIN
          C6STAGINGPERIODICO.EMIT MIT
          ON MIT.CODEMI=CATITEMREF.CODEMI 
		) PRODOTTI_A_CATALOGO 
			ON PRODOTTI_A_CATALOGO.CATALOGUEID = ALL_PATR_TERZI.PRODOTTO_IN_CATALOGO
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'DM_PATRIMONIO_TERZI_pre_selective' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'DM_PATRIMONIO_TERZI_pre_selective'
		)
END