-- =============================================  
-- Author:  <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
CREATE procedure [C6MartPeriodico].[RP_DM_PATRIMONIO_TERZI]  
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_PERIODICO  
  (Nome, Inizio, Nota)  
 VALUES (  
  'RP_DM_PATRIMONIO_TERZI',  
  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   
  VCONTRATTI.rete AS RETE,  
  VCONTRATTI.COD_FISCALE AS COD_FISCALE,  
  NULL AS NUM_POLIZZA,  
  (CASE RP_DettProdottiTerzi.tipoPolizza  
       WHEN 'CapitaleDifferito' THEN 'Capitale differito'  
       WHEN 'MistaImmediata' THEN 'Mista immediata'  
       WHEN 'VitaIntera' THEN 'Vita intera'  
       WHEN 'RenditaVitalizia' THEN 'Rendita vitalizia'  
       WHEN 'Capitalizzazione' THEN 'Capitalizzazione'  
       ELSE RP_DettProdottiTerzi.tipoPolizza  
     END) AS TIPO_POLIZZA,  
  COALESCE(RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_3,COALESCE(RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2,RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_1)) AS TIPO_PRODOTTO, --da rivedere  
  NULL AS TIPO_PRODOTTO2,  
  isnull(RP_CatalogoProdotti.catalogueID, 'PRODOTTO NON IN CATALOGO') AS COD_PRODOTTO,   
  ISNULL(MAPPING.Temp_oidProd, RP_PatrimonioTerzi.chiaveProdottoTerzi) AS COD_PRODOTTO_TERZI, --da rivedere prima era una stringona presa dal campo OID$  
  RP_DettProdottiTerzi.nomeIntermediario AS INTERMEDIARIO,  
  case when C6StagingPeriodico.RP_PatrimonioTerzi.tipoProd ='Assets.FinancialAssets.PolizzeRivalutabili'  
     then isnull(RP_DettProdottiTerzi.DTSOTTOSCRIZIONE, RP_PatrimonioTerzi.DATACTV)   
     when C6StagingPeriodico.RP_PatrimonioTerzi.tipoProd = 'Assets.FinancialAssets.ProdottiPrevidenziali'  
     then RP_PatrimonioTerzi.DATACTV  
     else isnull(RP_DettProdottiTerzi.dtInizioInvest,RP_PatrimonioTerzi.DATACTV)   
  end AS DATA_SOTTOSCRIZIONE,  
  RP_PatrimonioTerzi.dtFine AS DATA_SCADENZA,  
  RP_DettProdottiTerzi.tipoVersamento AS FREQ_RATA,  
  NULL AS DURATA_POLIZZA,  
  NULL AS PREMIO,  
  NULL AS ALIQUOTA,  
  NULL AS DATA_SOTT_FP,  
  NULL AS DATA_REGOLAMENTO,  
  NULL AS QUANTITA,  
  NULL AS PREZZO, 
  --------------------- intervento del 15/03/2018 per gestire ctv e capitale_investito più grandi del consentito
		  --RP_PatrimonioTerzi.ctv AS CTV,  
		  --RP_DettProdottiTerzi.capitaleInvestito AS VERSATO_NETTO,  
  case 
		   when RP_PatrimonioTerzi.ctv > 999999999999.00 then 999999999999.00
		   else RP_PatrimonioTerzi.ctv
		end  AS CTV,
		case 
		   when RP_DettProdottiTerzi.capitaleInvestito > 999999999999.00 then 999999999999.00
		   else RP_DettProdottiTerzi.capitaleInvestito
		end  AS VERSATO_NETTO,
  ---------------------  fine intervento del 15/3/2018
  NULL AS DIV_REGOLAMENTO,  
  NULL AS CAMBIO,  
  CASE   
   WHEN RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2 = 'Fondi'   
    THEN RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2  
   WHEN RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2 = 'Gestioni patrimoniali'   
    THEN RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_2  
   WHEN (RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_1 = 'Amministrato'   
     AND RP_PatrimonioTerzi.tipoProd NOT IN( 'Assets.FinancialAssets.CurrentAccount', 'Assets.FinancialAssets.Other'))  
    THEN 'Prodotti di Tipo amministrato (escluso C/C)'  
   WHEN (RP_S4_S5_S13_TRAD_UNTIL_LIV3.ALBERO_GERARCHIA_3 IN ('Index linked', 'Unit linked')  
     or (RP_S4_S5_S13_TRAD_UNTIL_LIV3.albero_gerarchia_1='Gestito Assicurativo previdenziale'   
      and RP_S4_S5_S13_TRAD_UNTIL_LIV3.albero_gerarchia_2='Assicurativi Vita'   
      and RP_S4_S5_S13_TRAD_UNTIL_LIV3.albero_gerarchia_3 is null))  
    THEN 'Assicurativi Unit/Index Linked'
	/* 20170427 aggiunto Assets.FinancialAssets.OtherFin e Assets.FinancialAssets.OtherAss al gruppo Portafoglio Generico */   	
   --WHEN RP_PatrimonioTerzi.tipoProd = 'Assets.FinancialAssets.Other'  
     WHEN RP_PatrimonioTerzi.tipoProd in ('Assets.FinancialAssets.Other', 'Assets.FinancialAssets.OtherFin', 'Assets.FinancialAssets.OtherAss')
    THEN 'Portafoglio Generico'  
   WHEN RP_PatrimonioTerzi.tipoProd IN ('Assets.FinancialAssets.ProdottiPrevidenziali', 'Assets.FinancialAssets.PolizzeRivalutabili')    
    THEN NULL  
   ELSE NULL   
  END AS DESCR_GRUPPO_PRODOTTO,  
  --'' AS DESCR_GRUPPO_PRODOTTO, --se mi dici adesso come si chiama questo campo (ALL_PATR_TERZI.DESCRIZIONE_PRODOTTO) è fatta  
  RP_CatalogoProdotti.AzOb AS STRTYPE, --manca (farselo dire da Alessia) (obbligazionario o azionario)  
  RP_CatalogoProdotti.Complessita AS COMPLEX,  
  RP_AnEmittente.Descrizione AS EMITTENTE,  
  MAPPING.Temp_oidProd AS chiave_thr, --da rivedere  
  @ID_ELAB AS ID_ELAB,  
  @TIPO_ELAB AS TIPO_ELAB  
 FROM  
  [C6MartPeriodico].[RP_vContrattiPerGenerazioneReport] VCONTRATTI  
 INNER JOIN  
  C6StagingPeriodico.RP_PatrimonioTerzi  
  ON VCONTRATTI.chiaveClientePB = RP_PatrimonioTerzi.chiaveClientePB  
 INNER JOIN  
  C6StagingPeriodico.RP_DettProdottiTerzi  
  ON RP_PatrimonioTerzi.chiaveProdottoTerzi = RP_DettProdottiTerzi.chiaveProdottoTerzi  
 LEFT JOIN    
        C6StagingPeriodico.RP_MappingTerzi MAPPING    
  ON RP_PatrimonioTerzi.chiaveProdottoTerzi = MAPPING.chiaveProdottoTerzi  
 INNER JOIN  
  C6StagingPeriodico.RP_S4_S5_S13_TRAD_UNTIL_LIV3 ON C6StagingPeriodico.RP_DettProdottiTerzi.alberatura = C6StagingPeriodico.RP_S4_S5_S13_TRAD_UNTIL_LIV3.alberatura  
 LEFT JOIN  
  C6StagingPeriodico.RP_CatalogoProdotti ON C6StagingPeriodico.RP_DettProdottiTerzi.chiaveProdotto = C6StagingPeriodico.RP_CatalogoProdotti.ChiaveProdotto  
 LEFT JOIN  
  C6StagingPeriodico.RP_AnEmittente ON C6StagingPeriodico.RP_CatalogoProdotti.Emittente = C6StagingPeriodico.RP_AnEmittente.Emittente  
 -- Log esecuzione  
 UPDATE  
  LOG_ESECUZIONE_PERIODICO  
 SET  
  Fine = GETDATE(),  
  Tipo = 'INSERT',  
  Righe = @@ROWCOUNT  
 WHERE  
  Nome = 'RP_DM_PATRIMONIO_TERZI' AND  
  Inizio = (  
   SELECT  
    MAX(Inizio)  
   FROM  
    LOG_ESECUZIONE_PERIODICO  
   WHERE  
    Nome = 'RP_DM_PATRIMONIO_TERZI'  
  )  
END