--select count(*) from c6stagingperiodico.spb_contr_sintesi2
CREATE procedure [C6StagingPeriodico].[RP_ST_SPB_CONTR_SINTESI2]
AS
BEGIN
	--TRUNCATE TABLE [C6StagingPeriodico].[SPB_CONTR_SINTESI2]
	--INSERT INTO [C6StagingPeriodico].[SPB_CONTR_SINTESI2]
	--		   ([POSITIONID]
	--		  ,[DTTRIM]
	--		  ,[DTRIFE]
	--		  ,[RETE]
	--		  ,[CODFIS]
	--		  ,[CODCONF]
	--		  ,[NUMPOL]
	--		  ,[CONTO]
	--		  ,[RUBR]
	--		  ,[CUSTGAR]
	--		  ,[TIPPROD]
	--		  ,[CODINT]
	--		  ,[CODPROD]
	--		  ,[SUBPROD]
	--		  ,[MAF]
	--		  ,[CODABI]
	--		  ,[PCTKEY]
	--		  ,[DESTIT]
	--		  ,[CTV]
	--		  ,[PARTVIA]
	--		  ,[PARTVIAAL]
	--		  ,[DTSOTTOS]
	--		  ,[RENDINIZ]
	--		  ,[RENDANNO]
	--		  ,[VALORE]
	--		  ,[SUBSET]
	--		  ,[CVN]
	--		  ,[SALRISC]
	--		  ,[ISIN])
	SELECT 
		   NULL AS [POSITIONID]
	      ,[DTTRIM]
		  ,[DTRIFE]
          ,[CS].[RETE]
		  ,C6.cod_fiscale AS CODFIS
		  ,[CODCONF]
		  ,[NUMPOL]
		  ,[CONTO]
		  ,case when (tipprod = 'CC' and rubr =' ') then '000' else RUBR end as RUBR
		  ,case when (tipprod = 'CC' and rubr =' ') then '0' else CUSTGAR end as custgar
		  ,[TIPPROD]
		  ,[CODINT]
		  ,[CODPROD]
		  ,[SUBPROD]
		  ,[MAF]
		  ,[CODABI]
		  ,case when (tipprod = 'CC' and rubr =' ') then '0' else PCTKEY end as pctkey
		  ,[DESTIT]
		  ,[CTV]
		  ,[PARTVIA]
		  ,[PARTVIAAL]
		  ,[DTSOTTOS]
		  ,[RENDINIZ]
		  ,[RENDANNO]
		  ,[VALORE]
		  ,[SUBSET]
		  ,[CVN]
		  ,[SALRISC]
		  ,'NA' as ISIN 
    INTO #CS_MENO_K_L191
	FROM C6StagingPeriodico.WSEICS CS
	JOIN C6StagingPeriodico.appo_contratti C6
	ON (case when cs.codman <> '' then 'FF@'+ cs.codman else cs.codfis end) = C6.COd_fiscale
	AND CS.RETE = C6.RETE  
	AND CS.SUBPROD NOT IN ('KE','KS','KW','K1','K2','K3','K4','K5','K6','K7', 'L191')
	AND CS.DTTRIM = c6martperiodico.gettrimestre2(getdate(),0)
	SELECT
		  NULL AS [POSITIONID]
	      ,CS.[DTTRIM]
		  ,CS.[DTRIFE]
          ,[CS].[RETE]
		  ,C6.cod_fiscale AS CODFIS
		  ,CS.[CODCONF]
		  ,[NUMPOL]
		  ,[CONTO]
		  ,case when (tipprod = 'CC' and rubr =' ') then '000' else RUBR end as RUBR
		  ,case when (tipprod = 'CC' and rubr =' ') then '0' else CUSTGAR end as custgar
		  ,CASE WHEN UL.ISIN IS NULL OR UL.SUBPROD = 'CL05' THEN 'ASU1' ELSE 'ASU2' end as TIPPROD
		  ,UL.[CODINT]
		  ,[CODPROD]
		  ,UL.[SUBPROD]
		  ,UL.[MAF]
		  ,[CODABI]
		  ,case when (tipprod = 'CC' and rubr =' ') then '0' else PCTKEY end as pctkey
		  ,[DESTIT]
		  ,UL.[CTV]
		  ,[PARTVIA]
		  ,[PARTVIAAL]
		  ,[DTSOTTOS]
		  ,[RENDINIZ]
		  ,[RENDANNO]
		  ,UL.[CTV] AS VALORE 
		  ,UL.[SUBSET]
		  ,[CVN]
		  ,[SALRISC]
		  ,case when(UL.ISIN is null) then 'NA' else UL.ISIN end as ISIN
	  INTO #UL_1
	  FROM C6StagingPeriodico.WSEICS CS 
	  join C6StagingPeriodico.appo_contratti C6
  		ON CS.RETE = C6.RETE 
		AND (case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
		AND CS.[DTTRIM] = c6martperiodico.gettrimestre2(getdate(),0)
	  JOIN C6STAGINGPERIODICO.WSEIUL AS UL
		ON	CS.CODCONF = UL.CODCONF
		AND	CS.DTTRIM = UL.DTTRIM
	  WHERE   
	      CS.SUBPROD  IN ('KE','KS','KW','K1') and ul.subprod not in('VAR5','VAR10','VAR15','CL05','LIQUI')
	  SELECT 
		DISTINCT
		   NULL AS [POSITIONID]
	      ,CS.[DTTRIM]
		  ,CS.[DTRIFE]
          ,[CS].[RETE]
		  ,C6.COd_fiscale AS CODFIS
		  ,CS.[CODCONF]
		  ,[NUMPOL]
		  ,[CONTO]
		  ,case when (tipprod = 'CC' and rubr =' ') then '000' else RUBR end as RUBR
		  ,case when (tipprod = 'CC' and rubr =' ') then '0' else CUSTGAR end as custgar
		  ,'ASU1' as TIPPROD
		  ,UL.[CODINT]
		  ,[CODPROD]
		  ,UL.[SUBPROD]
		  ,'NA' as MAF
		  ,[CODABI]
		  ,case when (tipprod = 'CC' and rubr =' ') then '0' else PCTKEY end as pctkey
		  ,[DESTIT]
		  ,SUM(UL.[CTV]) OVER (partition by UL.codConf) as CTV
		  ,[PARTVIA]
		  ,[PARTVIAAL]
		  ,[DTSOTTOS]
		  ,[RENDINIZ]
		  ,[RENDANNO]
		  ,[VALORE]
		  ,UL.[SUBSET]
		  ,[CVN]
		  ,[SALRISC]
		  ,'NA' as ISIN
	  INTO #UL_2	  
	  FROM C6StagingPeriodico.WSEICS CS 
	  join C6StagingPeriodico.appo_contratti C6
		ON CS.RETE = C6.RETE 
		AND (case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
		AND CS.DTTRIM = c6martperiodico.gettrimestre2(getdate(),0)
	  JOIN C6STAGINGPERIODICO.WSEIUL AS UL
		ON	CS.CODCONF = UL.CODCONF
		AND	CS.DTTRIM = UL.DTTRIM
	  WHERE 
          (
			CS.SUBPROD  IN ('KE','KS','KW','K1') and ul.subprod in('VAR5','VAR10','VAR15','CL05','LIQUI') 
			OR 
			(CS.SUBPROD  IN ('K2','K3','K4','K5','K6','K7')) 
		   )
--inizio intervento per Omnia linea Self
UNION ALL
SELECT
	POSITIONID
	,DTTRIM
	,DTRIFE
	,RETE
	,CODFIS
	,CODCONF
	,NUMPOL
	,CONTO
	,RUBR
	,custgar
	,TIPPROD
	,CODINT
	,CODPROD
	,SUBPROD
	,MAF
	,CODABI
	,pctkey
	,DESTIT
	,sum(CTV) as CTV
	,PARTVIA
	,PARTVIAAL
	,DTSOTTOS
	,RENDINIZ
	,RENDANNO
	,sum(VALORE) as VALORE
	,SUBSET
	,CVN
	,SALRISC
	,ISIN
FROM
	(
	SELECT
		NULL AS POSITIONID
		,CS.DTTRIM
		,CS.DTRIFE
		,CS.RETE
		,CASE WHEN CS.CODMAN ='' THEN CS.CODFIS ELSE 'FF@'+CS.CODMAN END AS CODFIS
		,CS.CODCONF
		,NUMPOL
		,CONTO
		,case when (GP.ISIN = 'EURO00000009' and rubr =' ') then '000' else RUBR end as RUBR
		,case when (GP.ISIN = 'EURO00000009' and rubr =' ') then '0' else CUSTGAR end as custgar
		,'GP' as TIPPROD
		,CODINT
		,CODPROD
		,SUBPROD
		,'NA' as MAF  
		,CODABI
		,case when (GP.ISIN = 'EURO00000009' and rubr =' ') then '0' else PCTKEY end as pctkey
		,DESTIT
		,GP.CTV as CTV
		,0.00 AS PARTVIA
		,0 AS PARTVIAAL
		,DTSOTTOS
		,RENDINIZ
		,RENDANNO
		,GP.CTV AS VALORE
		,'' AS SUBSET  
		,CVN
		,SALRISC
		,GP.ISIN
	FROM C6StagingPeriodico.WSEICS CS
	join C6StagingPeriodico.appo_contratto C6
		ON CS.RETE = C6.RETE 
		AND (case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
        and cs.DTTRIM = c6martperiodico.gettrimestre2(getdate(),0)
		and   cs.subprod = 'L191'
	JOIN C6STAGINGPERIODICO.WSEIGP GP
		ON	CS.CODCONF = GP.CODCONF
		AND	CS.DTTRIM = GP.DTTRIM
 		and GP.ISIN is not null
	) a
GROUP BY
	POSITIONID
	,DTTRIM
	,DTRIFE
	,RETE
	,CODFIS
	,CODCONF
	,NUMPOL
	,CONTO
	,RUBR
	,custgar
	,TIPPROD
	,CODINT
	,CODPROD
	,SUBPROD
	,MAF
	,CODABI
	,pctkey
	,DESTIT
	,PARTVIA
	,PARTVIAAL
	,DTSOTTOS
	,RENDINIZ
	,RENDANNO
	,SUBSET
	,CVN
	,SALRISC
	,ISIN
	SELECT DISTINCT 
		NULL AS POSITIONID
		,CS.DTTRIM
		,CS.DTRIFE
		,CS.RETE
		,C6.COD_FISCALE AS CODFIS
		,CS.CODCONF
		,NUMPOL
		,CONTO
		,RUBR
		,CUSTGAR
		,'GE' as TIPPROD
		,CODINT
		,CODPROD
		,SUBPROD
		,'NA' as MAF 
		,CODABI
		,PCTKEY
		,DESTIT
		,0.00 AS CTV
		,PARTVIA
		,PARTVIAAL
		,DTSOTTOS
		,RENDINIZ
		,RENDANNO
		,0.00 AS VALORE
		,'' AS SUBSET 
		,0.00 as CVN
		,SALRISC
		,'NA' as ISIN
	INTO #L191
	FROM  C6StagingPeriodico.WSEICS CS 
	join  C6StagingPeriodico.appo_contratti C6
			ON CS.RETE = C6.RETE 
			AND (case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
			AND CS.DTTRIM = c6martperiodico.gettrimestre2(getdate(),0)
			and CS.subprod = 'L191'
			and CS.partvia <> 0.00
END