CREATE     procedure [C6MartPeriodico].[PL_S186DettaglioPortafoglioSei] --'F','00545400483'
 -- Add the parameters for the stored procedure here
 @Rete char(1), 
 @CodiceFiscale varchar(16)
AS
BEGIN
declare @appo table
(
  	[BFCTV] [decimal](15, 2) NULL,
	[PatrimonioTerziCTV] [decimal](15, 2) NULL,
	[FinanziarioCTV] [decimal](16, 2) NULL,
	[FinanziarioPerc] [decimal](6, 2) NULL,
	[ImmobiliareCTV] [decimal](15, 2) NULL,
	[ImmobiliarePerc] [decimal](6, 2) NULL,
	[AltroCTV] [decimal](15, 2) NULL,
	[AltroPerc] [decimal](6, 2) NULL,
	[TotaleCTV] [decimal](15, 2) NULL,
	[ImmobiliarePQCTV] [decimal](15, 2) NULL
)
--creo le tabelle di appoggio
insert into @appo exec [C6MartPeriodico].[PL_D2_S152Patrimonio]  @Rete,@CodiceFiscale
declare @TotCTV [decimal](15, 2) =(select [BFCTV] from @appo)
SELECT
--distinct
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) AS dataSottoscrizione, 
PATRBF.ID_CONTRATTO AS ContrattoDossier, 
--V
CASE WHEN PATRBF.TIPO_PRODOTTO <> 'CC' THEN ANAGPROD.DESCR_PRODOTTO
	 ELSE ANAGPROD.DESCR_PRODOTTO+' '+SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC))
END AS Descrizione,
--Bido to get Code_CC field
--NOT AT ALL ANAGPROD.DESCR_PRODOTTO + ' ' + (STUFF(substring(ANAG_CC.COD_CC  , 4 , len(ANAG_CC.COD_CC) - 3), 3 , 0, '/') ) AS Descrizione,
--End Bido
--ANAGPROD.DESCR_PRODOTTO AS Descrizione,
PATRBF.CTV AS Controvalore,
--SUM(PATRBF.CTV) OVER() AS SommaProdottiEsclusi,
SUM(CASE 
        WHEN PATRBF.TIPO_PRODOTTO = 'CC' AND PATRBF.CTV < 0 THEN 0 
        ELSE PATRBF.CTV 
    END) OVER() AS SommaProdottiEsclusi, --per escludere i CC a Saldo Negativo
@TotCTV as PatrimonioFideuram,
@TotCTV-SUM(CASE 
        WHEN PATRBF.TIPO_PRODOTTO = 'CC' AND PATRBF.CTV < 0 THEN 0 
        ELSE PATRBF.CTV 
    END) OVER() AS PortafoglioSei
--@TotCTV-SUM(PATRBF.CTV) OVER() as PortafoglioSei
FROM 
C6MartPeriodico.PATRIMONIO_BF PATRBF
INNER JOIN 
C6MartPeriodico.ANAG_PRODOTTI ANAGPROD 
ON 
	ANAGPROD.COD_PRODOTTO = PATRBF.COD_PRODOTTO
--INNER JOIN 
--C6MartPeriodico.AREA_BISOGNO AREA 
--ON 
--	--LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA 
--	(LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA and AREA.ID_AREA <>'self') or (PATRBF.ID_AREA='self' and  AREA.ID_AREA ='self')
--LEFT JOIN 
--C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_PROD 
--ON 
--	PATRBF.RETE= RISCHIO_PROD.RETE
--	AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
--	AND RISCHIO_PROD.COD_AGGREG = 
--				CASE 
--					WHEN (PATRBF.NOME_PROGETTO IS NOT NULL)
--						THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+ 
--						 '|' + isnull(cast(patrbf.CHIAVE_PROGETTO as varchar),'') + '|'
--					when LEFT(PATRBF.ID_AREA,3)='Na' then 'RISFIN|NA|'
--					ELSE 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' 
--				END
----		END + PATRBF.POSITION_ID 
--		+ PATRBF.POSITION_ID 
--LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_PROG
--	ON PATRBF.RETE= RISCHIO_AGGR_PROG.RETE
--	AND PATRBF.COD_FISCALE= RISCHIO_AGGR_PROG.COD_FISCALE
--	AND RISCHIO_AGGR_PROG.COD_AGGREG = 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO
--		+ '|' +  isnull(cast(patrbf.CHIAVE_PROGETTO as varchar),'')
--LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_AREA
--	ON PATRBF.RETE= RISCHIO_AGGR_AREA.RETE
--	AND PATRBF.COD_FISCALE= RISCHIO_AGGR_AREA.COD_FISCALE
--	AND RISCHIO_AGGR_AREA.COD_AGGREG =   case 
--		when LEFT(PATRBF.ID_AREA,3) = 'Na' then 'RISFIN|NA'
--         else  'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) 
--		end
----	AND RISCHIO_AGGR_AREA.COD_AGGREG = 
----		CASE AREA.ID_AREA 
----			WHEN 'Na' THEN 'RISORSENONASSOCIATE' 
----			ELSE 'COMPLESSIVO|BF|' + LEFT(PATRBF.ID_AREA,3) 
----		END
--LEFT JOIN
--C6MartPeriodico.MONITORAGGIO_ATTUALE MONIT
--ON
--	PATRBF.RETE = MONIT.RETE
--	AND PATRBF.COD_FISCALE = MONIT.COD_FISCALE
--	AND LEFT(PATRBF.ID_AREA, 3) = MONIT.ID_AREA
--	AND CASE WHEN PATRBF.NOME_PROGETTO IS NULL THEN 'XXX' ELSE PATRBF.NOME_PROGETTO END = MONIT.NOME_PROGETTO
--	And patrbf.ordinamento_progetto=monit.ordinamento_progetto
----Bido to get Code_CC field
LEFT OUTER JOIN C6MartPeriodico.ANAG_CC
ON 
	ANAG_CC.COD_FISCALE = PATRBF.COD_FISCALE
	AND ANAG_CC.RETE = PATRBF.RETE
	AND cast(ANAG_CC.cod_cc as integer ) = substring(PATRBF.position_id,72,9)
		--aggiunta 18/12/2022 per APC103
--left join C6MartPeriodico.WSEICC apc
--on 
--	apc.RETE=PATRBF.RETE
--	and	apc.cod_fiscale=patrbf.cod_fiscale
--	and PATRBF.ID_CONTRATTO = --apc.CODCONFPR
--	(case when PATRBF.tipo_prodotto='CC' then apc.CODCONFC else apc.CODCONFPR end)
--	--fine aggiunta 18/12/2022 per APC103
WHERE 1 = 1
	AND PATRBF.RETE = @Rete
	AND PATRBF.COD_FISCALE = @CodiceFiscale
    --  AND (
    --    (PATRBF.TIPO_PRODOTTO = 'CC' AND PATRBF.CTV > 0) OR --per eliminare i CC a saldo Negativo
    --    (PATRBF.TIPO_PRODOTTO <> 'CC' AND PATRBF.CTV <> 0)
    --)
	AND PATRBF.Nob_Prez='S'
order by PATRBF.CTV desc
END