-- =============================================  
-- Description: <La sp legge i dati, rispettivamente, dalle seguenti tb (scaricate da DB2):   
-- C6StagingPeriodico.WSEIGP e C6StagingPeriodico.WSEIUL, e con tali dati popola la tb dbo.C6MartPeriodico.CAPPROT  
--N.B.: Le tb sono in join con la tb c6stagingperiodico.rp_catalogoprodotti, ciò è dettato dalla necessità di reperire il nome del fondo>  
-- =============================================  
--[C6MartPeriodico].[DM_CAPPROT_FVI]
CREATE procedure [C6MartPeriodico].[DM_CAPPROT_FVI]  
AS  
BEGIN  
SET NOCOUNT ON;  
DECLARE @DataFineTrim VARCHAR(8)  
SET @DataFineTrim = c6martperiodico.getTrimestre2(getDate(),0)  
 -- Log esecuzione  
 INSERT INTO LOG_ESECUZIONE_PERIODICO  
  (Nome, Inizio, Nota)  
 VALUES (  
  'DM_CAPPROT_FVI',  
  GETDATE(),  
  'Elaborazione trimestrale'  
 )  
 TRUNCATE TABLE C6MartPeriodico.CAPPROT_FVI  
 INSERT INTO C6MartPeriodico.CAPPROT_FVI  
 (
[RETE],
[CODFIS],
[CODPROD],
[SUBPROD],
[DES_PROD],
[COD_CONF],
[CONTROVALORE_RIFERIMENTO],
[COMPONENTE_PERFORMANCE],
[COMPONENTE_PROTEZIONE],
[DES_PROT],
[CONTROVALORE_PROT],
[DATARIF],
[DES_PROD_TIT],
[somma_ctv]
 )  
 select   
 RETE,  
 CODFIS,  
 CODPROD,
 SUBPROD,
 DES_PROD,  
 COD_CONF, 
 CONTROVALORE_RIFERIMENTO, 
 COMPONENTE_PERFORMANCE,
 COMPONENTE_PROTEZIONE,
 DES_PROT,   
 CONTROVALORE_PROT, 
 DATARIF,  
 DES_PROD_TIT  ,
 -- Errore segnalato in certificazione campione ad Aprile 2018 - 
 -- cf PRNMCD44M17L682S/F -  id 4  -  verificare a pag 17/24 del report il totale patrimonio controvalore protetto ( non quadra il tot € 28.346,65) che non ritorna con le due linee blue e white pag  18/19
 --          IL TOTALE DELLA LINEA NON DEVE ESSERE RAGGRUPPATO PER CODCONF MA PER LINEA (SUBPROD)
 --
 --			    sum(CONTROVALORE_RIFERIMENTO) over  (partition by COD_CONF ) as tot_controvalore	
 sum(CONTROVALORE_RIFERIMENTO) over  (partition by codfis,COD_CONF,SUBPROD ) as tot_controvalore
 -- Fine modifica  Aprile 2018
 from( 
 select   
 LP.RETE AS RETE,  
 LP.CODFIS AS CODFIS,  
 LP.CODPROD AS CODPROD,
 LP.CODLINEA AS SUBPROD,
 CAT.NOMEPRODOTTO as DES_PROD,  
 LP.CODCONF AS COD_CONF, 
 CS.CTV AS CONTROVALORE_RIFERIMENTO, 
 -- correzione calcolo per errore documento di analisi - 18/12/2017
     --LP.[CTV PROT] - LP.CTVMON AS COMPONENTE_PERFORMANCE,
	 CS.CTV - LP.CTVMON AS COMPONENTE_PERFORMANCE,
 -- fine correzione calcolo per errore documento di analisi - 18/12/2017
 LP.CTVMON AS COMPONENTE_PROTEZIONE,
 'Quota e controvalore protetti *'  AS DES_PROT,   
 LP.[CTV_PROT] AS CONTROVALORE_PROT, 
 @DataFineTrim AS DATARIF,  
 case
	when lp.codlinea = 'CS80' then 'FV Insieme Linea My Blue Protection 80'
	when lp.codlinea = 'CT80' then 'FV Insieme Linea My White Protection 80'
 end
  AS DES_PROD_TIT  
  FROM C6StagingPeriodico.WSEILP LP  
  JOIN [C6StagingPeriodico].[wseics] CS
  ON 
  LP.RETE = CS.RETE  
  AND LP.CODFIS = CS.CODFIS 
  AND LP.CODCONF = CS.CODCONF 
  AND LP.CODLINEA = CS.SUBPROD
  LEFT OUTER join c6stagingperiodico.rp_catalogoprodotti CAT  
  on  
   cat.codmaf = 'RS_'+lp.codlinea
  and CAT.visibilita = 1  
WHERE LP.DTTRIM = @DataFineTrim  and cs.dttrim = @DataFineTrim and lp.codlinea in ('CS80','CT80')
union
select   
 LP.RETE AS RETE,  
 LP.CODFIS AS CODFIS,  
 LP.CODPROD AS CODPROD,
 LP.CODLINEA AS SUBPROD,
 'FV Sintonia Linea Protezione 85' as DES_PROD,  
 LP.CODCONF AS COD_CONF, 
/* 
  il controvalore di riferimento deve coincidere con la somma dei controvalori dei singoli fondi
   sottostanti alla linea (selezionati dal cliente e monetari movimentati dal Risk Provider) 
   e della gestione separata “FV Vivadue” 
*/
(select sum(CTV) from  [C6StagingPeriodico].[wseics] 
	where rete = LP.RETE   
  AND  CODFIS = LP.CODFIS  and dttrim = @DataFineTrim and CODCONF = LP.CODCONF and codprod = lp.codprod)
  AS CONTROVALORE_RIFERIMENTO, 
 -- correzione calcolo per errore documento di analisi - 18/12/2017
		---LP.[CTV PROT] - LP.CTVMON AS COMPONENTE_PERFORMANCE,
		(select sum(CTV) from  [C6StagingPeriodico].[wseics] 
	where rete = LP.RETE   
  AND  CODFIS = LP.CODFIS  and dttrim = @DataFineTrim and CODCONF = LP.CODCONF and codprod = lp.codprod) - LP.CTVMON AS COMPONENTE_PERFORMANCE,
 -- fine correzione calcolo per errore documento di analisi - 18/12/2017
 LP.CTVMON AS COMPONENTE_PROTEZIONE,
 'Quota e controvalore protetti *'  AS DES_PROT,   
 LP.[CTV_PROT] AS CONTROVALORE_PROT, 
 @DataFineTrim AS DATARIF,  
 'FV Sintonia Linea Protezione 85' AS DES_PROD_TIT  
  FROM C6StagingPeriodico.WSEILP LP  
  JOIN [C6StagingPeriodico].[wseics] CS
  ON 
  LP.RETE = CS.RETE  
  AND LP.CODFIS = CS.CODFIS 
  AND LP.CODCONF = CS.CODCONF 
  AND LP.CODLINEA = CS.SUBPROD
WHERE
 LP.DTTRIM = @DataFineTrim 
  and cs.dttrim = @DataFineTrim
   and lp.codlinea = 'MP'
    and lp.flagtrim = 'S' --creo un record per ogni contratto, prendendo i dati del record con i dati aggregati (flagtrim = 's')
   ) a
 -- Log esecuzione  
 UPDATE  
  LOG_ESECUZIONE_PERIODICO  
 SET  
  Fine = GETDATE(),  
  Tipo = 'INSERT',  
  Righe = @@ROWCOUNT  
 WHERE  
  Nome = 'DM_CAPPROT_FVI' AND  
  Inizio = (  
   SELECT  
    MAX(Inizio)  
   FROM  
    LOG_ESECUZIONE_PERIODICO  
   WHERE  
    Nome = 'DM_CAPPROT_FVI'  
  )  
END